How to select only 1 row from oracle sql?

Question

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

 SELECT user FROM DUAL 

... and he would have, for example, 40 records. But I need only one entry .... And, I want this to happen without a WHERE .

I need something in the table_name field, for example:

 SELECT FirstRow(user) FROM DUAL 

Answer 1

You are using ROWNUM.

t

 SELECT user FROM Dual WHERE ROWNUM = 1 

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

Answer 2

I found this “solution” hidden in one of the comments. Since I was looking for this for a while, I would like to highlight it a bit (I can not comment or do such things yet ...), so I used this:

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

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

Answer 3

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 any line or lines (plural) can be used regardless of the set of required number of lines.)

Answer 4

As far as I know, the dual table in Oracle is a special one-row table. So this would be enough:

 SELECT user FROM dual 

Answer 5

Is the answer:

You should use a subquery 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" TSQL.

Therefore, you cannot use a query like the following: "select * from any_table_x, where rownum = 1 order by any_column_x;" Since the oracle receives the first row, then order by sentence is applied.

Answer 6

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

Answer 7

"FirstRow" is a constraint and therefore it is placed in the where clause not in the select clause. And it's called rownum

 select * from dual where rownum = 1; 

Answer 8

We have 3 options for getting the first row in the Oracle database 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 

Answer 9

If any line will do, try:

 select max(user) from table; 

There is no where clause.

Answer 10

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

Answer 11

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

will work better, another option:

 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 need different subsets, but I think you can also use RANK() But I also like row_number() over(...) since grouping is not required.

Answer 12

More flexible than select max() is:

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