我如何在Oracle中排名第一?

How do I do the following?

我该怎么做?

select top 1 Fname from MyTbl

In Oracle 11g?

在Oracle 11 g ?

9 个解决方案

#1

If you want just a first selected row, you can:

如果你只想要第一个选定的行，你可以:

select fname from MyTbl where rownum = 1

You can also use analytic functions to order and take the top x:

你也可以用解析函数排序，取最上面的x:

select max(fname) over (rank() order by some_factor) from MyTbl

#2

SELECT *
  FROM (SELECT * FROM MyTbl ORDER BY Fname )
 WHERE ROWNUM = 1;

#3

With Oracle 12c (June 2013), you are able to use it like the following.

使用Oracle 12c(2013年6月)，您可以像下面这样使用它。

SELECT * FROM   MYTABLE
--ORDER BY COLUMNNAME -OPTIONAL          
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

#4

You could use ROW_NUMBER() with a ORDER BY clause in sub-query and use this column in replacement of TOP N. This can be explained step-by-step.

您可以使用ROW_NUMBER()在子查询中使用ORDER BY子句，并使用这一列替换顶部N.这可以一步一步地解释。

See the below table which have two columns NAME and DT_CREATED.

请参见下面的表，该表有两个列名称和DT_CREATED。

If you need to take only the first two dates irrespective of NAME, you could use the below query. The logic has been written inside query

如果您只需要取前两个日期而不考虑名称，您可以使用下面的查询。该逻辑已在查询中写入

-- The number of records can be specified in WHERE clause
SELECT RNO,NAME,DT_CREATED
FROM
(
    -- Generates numbers in a column in sequence in the order of date
    SELECT ROW_NUMBER() OVER (ORDER BY DT_CREATED) AS RNO,
    NAME,DT_CREATED
    FROM DEMOTOP
)TAB
WHERE RNO<3;

RESULT

结果

In some situations, we need to select TOP N results respective to each NAME. In such case we can use PARTITION BY with an ORDER BY clause in sub-query. Refer the below query.

在某些情况下，我们需要选择每个名字对应的前N个结果。在这种情况下，我们可以在子查询中使用ORDER BY子句。请参考以下查询。

-- The number of records can be specified in WHERE clause
SELECT RNO,NAME,DT_CREATED
FROM
(
  --Generates numbers in a column in sequence in the order of date for each NAME
    SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DT_CREATED) AS RNO,
    NAME,DT_CREATED
    FROM DEMOTOP
)TAB
WHERE RNO<3;

RESULT

结果

#5

You can do something like

你可以做一些类似的事情

    SELECT *
      FROM (SELECT Fname FROM MyTbl ORDER BY Fname )
 WHERE rownum = 1;

You could also use the analytic functions RANK and/or DENSE_RANK, but ROWNUM is probably the easiest.

您也可以使用分析函数RANK和/或DENSE_RANK，但是ROWNUM可能是最简单的。

#6

Use:

使用:

SELECT x.*
  FROM (SELECT fname 
          FROM MyTbl) x
 WHERE ROWNUM = 1

If using Oracle9i+, you could look at using analytic functions like ROW_NUMBER() but they won't perform as well as ROWNUM.

如果使用Oracle9i+，您可以使用像ROW_NUMBER()这样的解析函数，但它们的性能不如ROWNUM。

#7

select * from (
    select FName from MyTbl
)
where rownum <= 1;

#8

To select the first row from a table and to select one row from a table are two different tasks and need a different query. There are many possible ways to do so. Four of them are:

要从表中选择第一行，从表中选择一行是两个不同的任务，需要不同的查询。有很多可能的方法。其中四个是:

First

第一个

select  max(Fname) from MyTbl;

Second

第二个

select  min(Fname) from MyTbl;

Third

第三

select  Fname from MyTbl  where rownum = 1;

Fourth

第四

select  max(Fname) from MyTbl where rowid=(select  max(rowid) from MyTbl)

#9

I had the same issue, and I can fix this with this solution:

我也有同样的问题，我可以用这个解决方案来解决这个问题:

select a.*, rownum 
from (select Fname from MyTbl order by Fname DESC) a
where
rownum = 1

You can order your result before to have the first value on top.

您可以先对结果进行排序，使第一个值位于顶部。

Good luck

祝你好运