167

I want to use oracle syntax to select only 1 row from table DUAL. For example, I want to execute this query:

SELECT user 
  FROM DUAL

...and it'd have, like, 40 records. But I need only one record. ...AND, I want to make it happen without a WHERE clause.

I need something in the table_name field such as:

SELECT FirstRow(user) 
  FROM DUAL
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Ben
  • 1,914
  • 2
  • 13
  • 11
  • 1
    What version of Oracle? Using ROWNUM or ROW_NUMBER (9i+) would mean needing a WHERE clause – OMG Ponies Jan 19 '12 at 00:25
  • 1
    Did you name a table `dual` ? – ypercubeᵀᴹ Jan 19 '12 at 00:25
  • 3
    @ypercube `dual` is the system table in oracle –  Jan 19 '12 at 00:26
  • 1
    @bdares: Exactly. So, can someone create another one with same name? – ypercubeᵀᴹ Jan 19 '12 at 00:28
  • @ypercube no. the `dual` table does have a `user` column, though. The "no where clause" constraint seems... contrived? Why the heck not? –  Jan 19 '12 at 00:29
  • ROWNUM(9i+) , do you have an answer? – Ben Jan 19 '12 at 01:19
  • 4
    @Ben, you really shouldn't create a table called `DUAL`. It's a bit like `#define TRUE 0` in C - sure, it might work for you, but future developers will hate you. – Jeffrey Kemp Jan 19 '12 at 04:53
  • 3
    Have you actually tried to run `select user from dual`? If not, please try that, and see what you get. On a standard oracle system, you'll get back the user you are executing the command with. – Shannon Severance Jan 19 '12 at 06:38
  • 1
    possible duplicate of [How to do top 1 in Oracle?](http://stackoverflow.com/questions/3451534/how-to-do-top-1-in-oracle) – Dave Jarvis Aug 20 '13 at 18:29
  • Old question but I'm voting to close as unclear. `DUAL` only has one row. If you have two rows in it or another table called `dual` or you meant a different table then that changes the question. – William Robertson Mar 12 '18 at 13:23

14 Answers14

214

You use ROWNUM.

ie.

SELECT user FROM Dual WHERE ROWNUM = 1

http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

gdoron
  • 147,333
  • 58
  • 291
  • 367
mindvirus
  • 5,016
  • 4
  • 29
  • 46
  • @ypercube far as I can tell, it does. (At least it works for my installation of oracle10g.) –  Jan 19 '12 at 00:28
  • @bdares: it will work, yes. But not your answer, with the `order by`. – ypercubeᵀᴹ Jan 19 '12 at 00:30
  • 1
    Yes. ROWNUM is a special column that gets added to the result set enumerating the results. You can use it to select multiple as well, for example, if you wanted to find the 10 highest payed employees, you might say "SELECT user FROM Employees WHERE ROWNUM <= 10 ORDER BY SALARY DESCENDING" – mindvirus Jan 19 '12 at 00:30
  • 12
    @mkdess: No, `ORDER BY` is applied after the `WHERE`. – ypercubeᵀᴹ Jan 19 '12 at 00:32
  • 27
    You'd need: `SELECT * FROM (SELECT user FROM Employees ORDER BY SALARY DESC) WHERE ROWNUM <= 10` – ypercubeᵀᴹ Jan 19 '12 at 00:33
  • ROWNUM doesn't work, ROWNUM() works , and i don't know the version i need to ask at work anyone know the answer ? can't i use some special proc to avoid the where? – Ben Jan 19 '12 at 01:05
81

This syntax is available in Oracle 12c:

select * from some_table fetch first 1 row only;
select * from some_table fetch first 1 rows only;
select * from some_table fetch first 10 row only;
select * from some_table fetch first 10 rows only;

^^I just wanted to demonstrate that either row or rows (plural) can be used regardless of the plurality of the desired number of rows.)

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
mancini0
  • 4,285
  • 1
  • 29
  • 31
62

I found this "solution" hidden in one of the comments. Since I was looking it up for a while, I'd like to highlight it a bit (can't yet comment or do such stuff...), so this is what I used:

SELECT * FROM (SELECT [Column] FROM [Table] ORDER BY [Date] DESC) WHERE ROWNUM = 1

This will print me the desired [Column] entry from the newest entry in the table, assuming that [Date] is always inserted via SYSDATE.

  • I found it will also work if you order by `ROWID`, as long as you never delete any records and always care about the last inserted/modified one. – vapcguy Oct 07 '16 at 21:27
  • 1
    @vapcguy: Don't expect ROWID to be ordered, even if you never delete a row from the table! Even if it works for you now, it is never guaranteed to work in future versions. – D. Mika May 22 '17 at 08:10
  • @D.Mika Actually if it works now, and you never add/remove/update/delete records, there should never be any issues. The records can only be changed if you actually change them. There is this misconception that somehow `ROWID` is randomly modified by Oracle. It isn't. It is based on actually modifying the rows, i.e. you delete one, then insert one. The inserted one will get the old one's `ROWID`. There are such things as static tables that never get updated-like states in the U.S. is a good example-where if it changed, it would probably have other repercussions, anyway, when this is fine. – vapcguy May 22 '17 at 19:39
  • 1
    @vapcguy: Well, thats almost right. But there are other operations that will change the ROWID. What if you export / import the table for some reason? There are others operation, but some of them need ENABLE ROW MOVEMENT. I just want to say it's no good idea to rely on an implementation detail that may change in the future. – D. Mika May 27 '17 at 07:13
  • @D.Mika I'm sure if there are any operations where the `ROWID` could be changed, a good DBA would look them up and do what they could to avoid them if there was the possibility they were affecting such a static table as I described that only the application should be operating on. A table export can be done with a `SELECT` statement, instead. The import would happen once and then never again. I get your point, care is definitely needed, but the issues are far from unavoidable. – vapcguy May 30 '17 at 16:28
14

we have 3 choices to get the first row in Oracle DB table.

1) select * from table_name where rownum= 1 is the best way

2) select * from table_name where id = ( select min(id) from table_name)

3)

select * from 
    (select * from table_name order by id)
where rownum = 1
Massimiliano Kraus
  • 3,638
  • 5
  • 27
  • 47
Deva
  • 1,851
  • 21
  • 22
10

The answer is:

You should use nested query as:

SELECT *
FROM ANY_TABLE_X 
WHERE ANY_COLUMN_X = (SELECT MAX(ANY_COLUMN_X) FROM ANY_TABLE_X) 

=> In PL/SQL "ROWNUM = 1" is NOT equal to "TOP 1" of TSQL.

So you can't use a query like this: "select * from any_table_x where rownum=1 order by any_column_x;" Because oracle gets first row then applies order by clause.

Fuat
  • 789
  • 9
  • 14
  • 3
    Please add some clarification to your answer – hgwhittle Jan 09 '14 at 15:13
  • Unusual syntax should be avoided without a good reason. In this case, it would be helpful to provide either a test case or a bug number. I vaguely recall some weird issues with `rownum = 1`, but we shouldn't let old bugs affect our code anymore. – Jon Heller Jan 10 '14 at 03:26
  • 7
    @hgwhittle, The reason why Fuat is correct is because ROWNUM doesn't care about 'ordery by', it just grabs the first record it can find and immediately returns it. So in other words, the ROWNUM qualifier doesn't have any respect for "Order By" command. I wish that wasn't the case but Fuat is correct, to use the nested query. – Eric Milliot-Martinez Jul 20 '16 at 20:00
6

As far as I know, the dual table in Oracle is a special table with just one row. So, this would suffice:

SELECT user
FROM dual
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
5

There is no limit 1 condition (thats MySQL / PostgresSQL) in Oracle, you need to specify where rownum = 1.

Oh Chin Boon
  • 23,028
  • 51
  • 143
  • 215
4

If you want to get back only the first row of a sorted result with the least subqueries, try this:

select *
  from ( select a.*
              , row_number() over ( order by sysdate_col desc ) as row_num
           from table_name a )
  where row_num = 1;
David Buck
  • 3,752
  • 35
  • 31
  • 35
Jody Fedor
  • 41
  • 3
  • 1
    Where sysdate_col would be the name of any column you want to sort by and of course, table_name would be the name of the table you want the sorted data to come from. – Jody Fedor Jun 24 '20 at 14:26
4

"FirstRow" Is a restriction and therefor it's place in the where clause not in the select clause. And it's called rownum

select * from dual where rownum = 1;
gdoron
  • 147,333
  • 58
  • 291
  • 367
  • 2
    Note that this will not work as expected in combination with `ORDER BY`, since ordering only happens _after_ the where clause. In other words, to get the top of a certain sorted query, rownum is utterly useless. – Nyerguds Jan 17 '13 at 11:51
  • @Nyerguds, this is only half true. You can use order by before the `Where` with a View query. – gdoron Jan 17 '13 at 11:53
  • 4
    What, so `SELECT * FROM (SELECT * FROM ... WHERE ... ORDER BY ...) WHERE ROWNUM = 1`? Well, that may work, but it looks pretty dumb, tbh. – Nyerguds Jan 17 '13 at 12:03
2

If any row would do, try:

select max(user)  
from table;

No where clause.

Raihan
  • 10,095
  • 5
  • 27
  • 45
1
select name, price
  from (
    select name, price, 
    row_number() over (order by price) r
      from items
  )
where r between 1 and 5; 
Andrew
  • 7,619
  • 13
  • 63
  • 117
1

select a.user from (select user from users order by user) a where rownum = 1

will perform the best, another option is:

select a.user 
from ( 
select user, 
row_number() over (order by user) user_rank, 
row_number() over (partition by dept order by user) user_dept_rank 
from users 
) a 
where a.user_rank = 1 or user_dept_rank = 2

in scenarios where you want different subsets, but I guess you could also use RANK() But, I also like row_number() over(...) since no grouping is required.

MaartenDev
  • 5,631
  • 5
  • 21
  • 33
Tyler
  • 59
  • 1
0

More flexible than select max() is:

select distinct first_row(column_x) over (order by column_y,column_z,...) from Table_A
John Dvorak
  • 26,799
  • 13
  • 69
  • 83
Guest
  • 29
0

In oracle 11g, The below example can be helpful....

 SELECT *
 FROM
 (
    SELECT *
        
    FROM RF_ACTN_STATUS STS 
    WHERE 
        STS.RF_ACTN_TYPE_ID=27 AND 
        STS.ACTION_SORT>2 AND 
        NVL(STS.IS_RESEND,'N')='N' AND 
        NVL(STS.IS_CANCELLED_STATIUS,'N')='N'            
    ORDER BY STS.ACTION_SORT ASC
) X
WHERE ROWNUM = 1
        ;
Rejwanul Reja
  • 1,339
  • 1
  • 17
  • 19