CODEWITHSUNDEEP
sqloracle-databaseoracle9i
Ben
Ben

Reputation: 2024

How to select only 1 row from oracle sql?

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

Upvotes: 178

Views: 987391

Answers (14)

Fuat
Fuat

Reputation: 809

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.

Upvotes: 10

Rejwanul Reja
Rejwanul Reja

Reputation: 1491

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
        ;

Upvotes: 0

Jody Fedor
Jody Fedor

Reputation: 41

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;

Upvotes: 4

Tyler
Tyler

Reputation: 69

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.

Upvotes: 1

Deva
Deva

Reputation: 2121

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

Upvotes: 17

mancini0
mancini0

Reputation: 4703

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

Upvotes: 101

Andrew
Andrew

Reputation: 7768

select name, price
  from (
    select name, price, 
    row_number() over (order by price) r
      from items
  )
where r between 1 and 5;

Upvotes: 1

mindvirus
mindvirus

Reputation: 5246

You use ROWNUM.

ie. 

SELECT user FROM Dual WHERE ROWNUM = 1

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

Upvotes: 223

user3890681
user3890681

Reputation:

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.

Upvotes: 64

Guest
Guest

Reputation: 29

More flexible than select max() is:

select distinct first_row(column_x) over (order by column_y,column_z,...) from Table_A

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

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

Upvotes: 6

gdoron
gdoron

Reputation: 150243

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

Upvotes: 4

Raihan
Raihan

Reputation: 10395

If any row would do, try:

select max(user)  
from table;

No where clause.

Upvotes: 2

HJW
HJW

Reputation: 23443

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

Upvotes: 5

Related Questions