Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select only 1 row from oracle sql?

People also ask

How do I select row 1 in SQL?

While the table name is selected type CTRL + 3 and you will notice that the query will run and will return a single row as a resultset. Now developer just has to select the table name and click on CTRL + 3 or your preferred shortcut key and you will be able to see a single row from your table.

How do I select a specific row in Oracle?

You can do with an auxiliary operation. Firstly number the rows by row_number() function and then order by them : select * from ( select row_number() over (order by 0) rn, t.

How do you select the first row in PL SQL?

Description. The Oracle/PLSQL ROWNUM function returns a number that represents the order that a row is selected by Oracle from a table or joined tables. The first row has a ROWNUM of 1, the second has a ROWNUM of 2, and so on.

How do I select the last 3 rows in SQL?

I want to select the last 3 rows of an sql table. I know I should use SELECT * FROM table ORDER BY DESC LIMIT 3 , but the problem with this code is that it selects the rows from the end. For example, it selects 30, then 29, then 28. But, I need them in this format: 28, 29, 30 .


You use ROWNUM.

ie.

SELECT user FROM Dual WHERE ROWNUM = 1

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


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.


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


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

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