How do I do top 1 in Oracle? [duplicate]

Question

How do I do the following?

select top 1 Fname from MyTbl

In Oracle 11g?

Diligence

Have a look at How does one select the TOP N rows from a table – Microorganism

Can you tell us the order according to which you want 'top 1'? – Heaume

First of all you should never ever rely on the DB engine to do that, ever. If you want to know things like that, put in a sequencer. When you do that it is guaranteed that they will be numbered in the order they were inserted. – Autocratic

Very usful material on this topic use-the-index-luke.com/sql/partial-results/top-n-queries – Euphony

Answer 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:

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

Educt

This is good if you only want 1 row and don't care which. If you want specific rows, like the most recent record, you need to do the sort in a subselect, like Vash's answer. Oracle assigns rownums before the sort. – Meuser

@Scott yup. that is correct. And Patrick, good point I think the syntax is incorrect on that. It really should be a keep over (dense_rank() last... ) – Educt

The difference between the first and second example is that the first one selects A row (any row, with no order). The second example gets the value of the first row, without doing an order inner query (as per examples below). – Salvo

The syntax in not correct in: select max(fname) over (rank() order by some_factor) from MyTbl – Conall

Impressed by the analytic function, not a very well known functionality of oracle – Frustule

@Frustule "not a very well known functionality of oracle" - With respect, I beg to differ. This matters because "not well known functionality" tends to imply obscurity and hence could suggest we should avoid using. This is not obscure, and its use should not be avoided. – Samirasamisen

@Sepster, i agree that it should not be avoided, it's just that in my experience i haven't met many people who do know about this functionality – Frustule

The second query should be select max(fname) keep (dense_rank first order by some_factor ) from MyTbl. – Ironsmith

Answer 2

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

Fanniefannin

This answer correctly gets the TOP row (orders the results before restricting on ROWNUM). – Salvo

This answer is not an exact translation - the original query doesn't have an ORDER BY, nor does it return all columns in the table. – Impuissant

I stand corrected (see below). Will switch votes once time is up. – Salvo

@OMGPonies yeah. but its probably what most people actually want who come to this page via googling their problem – Inpour

This for sure must be the winning answer in this thread. I might add a note that for top X one can change it to WHERE ROWNUM <= X – Badderlocks

worked for me! this answer correctly gives me the first row (ordered descending) – Redcap

Answer 3

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

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

Baseball

Interesting command, I'm using 12c here and the OFFSET 0 ROWS apparently is not necessary, you can use FETCH NEXT 1 ROWS ONLY or even FETCH FIRST ROW ONLY, the order by is important or it will be equivalent to just using a WHERE rownum = 1. I've even tried it in an OUTER APPLY instruction and it worked like Ms-SQL's TOP function there. – Salify

You are right @RafaelMerlin. After your post I recognized that OFFSET 0 ROWS is not necessary. It would be useful when retrieving data between top X and top Y. – Baseball

More examples : oracle-base.com/articles/12c/… – Tiruchirapalli

So far so good, with an important missing point which's TIES. Refer this for the cases when ties occur for version 12c + and 12c - – Ironsmith

Answer 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.

See the below table which have two columns NAME and 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.

-- 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

Abroad

Using ROW_NUMBER()... is more correct solution than in topic answer. One problem with this solution (and with max(field) variant too) that you cannot do things like "select ... (select ROW_NUMBER() ... ) for update;" – Sudan

And it's sometimes very important in PL/SQL (sorry, failed to edit previous comment in 5 minutes limit). – Sudan

In such case we can use CTE as in the outer part. Right? @Alexo Po. – Abroad

I think I do not understand you. for update clause can be used when ROWID is "easily" preserved by Oracle. So grouping (and grouping due to analytic clause usage) hides real ROWID and rows cannot be locked. And second, CTE (with (select ... ) as clause) does not change anything to this problem, CTE just aims in reading and supporting queries. Right? @Sarath Avanavu – Sudan

Note on myself. The problem with ROWID actually happens specifically because of where RNO<3 condition, in this case value of RNO is not connected with ROWID so that is why Oracle cannot lock rows. – Sudan

Simpler topic solution (without analytic funcs) given here: #471042 – Sudan

Answer 5

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

Solanum

Answer 6

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.

Bawbee

can you please help with some example of rank etc. – Leighleigha

The query does not work. – Protractor

Answer 7

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.

Impuissant

Nice answer but contains a tiny typo. Where you say Oracle9i+ shouldn't that be 8i? download-west.oracle.com/docs/cd/A87860_01/doc/server.817/… – Usa

@carpenteri: True, analytics were available in 8i - can't remember the details of, but analytics weren't really available to the public until 9i. – Impuissant

Small comment - Vash's answer below includes an ORDER BY on the inner query which is critical if you want the TOP value of fname, rather than 'first' (which can be anything, most likely first row inserted). Might be worth an edit? – Salvo

@JulesLt: The query provided by the OP doesn't include an ORDER BY, so this is answer represents and exact translation to Oracle syntax. – Impuissant

My misunderstanding of the SQL SERVER TOP syntax (erroneously presumed that it was similar to FIRST in RANK, not ROWNUM). Voted up. – Salvo

@JulesLt: No worries, likely part of why TOP wasn't accepted as the ANSI means of limited the resultset outside of filtration. – Impuissant

@OMGPonies: analytics were available in SQL but not in PL/SQL in 8i. You had to use EXECUTE IMMEDIATE to use them in a PL/SQL block. – Gewgaw

@Quassnoi: Oh? I think it was APC who said that analytics were only available on a particular version of 8i. So the dynamic SQL would circumvent the PLSQL evaluation? – Impuissant

@OMGPonies: 8 and 8i were different versions, and only the latter supported the analytics (since R2, AFAIR). SQL and PL/SQL had different parsers, the procedural one did not understand analytics and some other things. EXECUTE IMMEDIATE was parsed with SQL parser. They were merged in 9i. – Gewgaw

@OMGPonies: when 8i R2 was released in 2000, we had a meeting on which I insisted on upgrading all our clients using previous versions (over 200 installations by that time) and stop supporting 8. Developers in that company still consider it the most important solution, since the next end of support happened only in 2009. – Gewgaw

@Quassnoi: Yeah, I can see how a consistent approach would be best--for both development and support. I can't remember if it was 8 or 8i that we used in school, but we certainly weren't using analytics or dynamic SQL. – Impuissant

Answer 8

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

Cloutman

Answer 9

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)

Triphammer