296

How do I do the following?

select top 1 Fname from MyTbl

In Oracle 11g?

Improve this question
4
  • Have a look at How does one select the TOP N rows from a table Commented Aug 10, 2010 at 17:12
  • 3
    Can you tell us the order according to which you want 'top 1'? Commented Nov 26, 2014 at 3:34
  • 1
    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. Commented Feb 9, 2015 at 4:25
  • 1
    Very usful material on this topic use-the-index-luke.com/sql/partial-results/top-n-queries Commented Oct 28, 2015 at 13:10

9 Answers 9

Reset to default
307

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

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.
@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... )
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).
The syntax in not correct in: select max(fname) over (rank() order by some_factor) from MyTbl
@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
|
193 
SELECT * FROM (SELECT * FROM MyTbl ORDER BY Fname ) WHERE ROWNUM = 1;
Improve this answer

6 Comments

This answer correctly gets the TOP row (orders the results before restricting on ROWNUM).
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.
I stand corrected (see below). Will switch votes once time is up.
@OMGPonies yeah. but its probably what most people actually want who come to this page via googling their problem
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
|
62

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
Improve this answer

4 Comments

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.
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.
More examples : oracle-base.com/articles/12c/…
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 -
13

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.

enter image description here

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

enter image description here

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

enter image description here

Improve this answer

6 Comments

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;"
And it's sometimes very important in PL/SQL (sorry, failed to edit previous comment in 5 minutes limit).
In such case we can use CTE as in the outer part. Right? @Alexo Po.
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
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.
|
11 
select * from ( select FName from MyTbl ) where rownum <= 1;
Improve this answer

Comments

10

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.

Improve this answer

2 Comments

can you please help with some example of rank etc.
The query does not work.
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.

Improve this answer

11 Comments

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/…
@carpenteri: True, analytics were available in 8i - can't remember the details of, but analytics weren't really available to the public until 9i.
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?
@JulesLt: The query provided by the OP doesn't include an ORDER BY, so this is answer represents and exact translation to Oracle syntax.
My misunderstanding of the SQL SERVER TOP syntax (erroneously presumed that it was similar to FIRST in RANK, not ROWNUM). Voted up.
|
4

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)
Improve this answer

Comments

3

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.