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
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.)
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
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.
There is no limit 1
condition (thats MySQL / PostgresSQL) in Oracle, you need to specify where rownum = 1
.
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.
If any row would do, try:
select max(user)
from table;
No where clause.
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.
You use ROWNUM.
ie.
SELECT user FROM Dual WHERE ROWNUM = 1
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
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;
select name, price
from (
select name, price,
row_number() over (order by price) r
from items
)
where r between 1 and 5;
"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;
More flexible than select max()
is:
select distinct first_row(column_x) over (order by column_y,column_z,...) from Table_A
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
Source: Stackoverflow.com