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
dual
?
dual
is the system table in oracle
DUAL
. It's a bit like #define TRUE 0
in C - sure, it might work for you, but future developers will hate you.
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.
You use ROWNUM.
ie.
SELECT user FROM Dual WHERE ROWNUM = 1
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
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.)
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.
ROWID
, as long as you never delete any records and always care about the last inserted/modified one.
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.
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.
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.
rownum = 1
, but we shouldn't let old bugs affect our code anymore.
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
There is no limit 1
condition (thats MySQL / PostgresSQL) in Oracle, you need to specify where rownum = 1
.
"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;
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.
Where
with a View query.
SELECT * FROM (SELECT * FROM ... WHERE ... ORDER BY ...) WHERE ROWNUM = 1
? Well, that may work, but it looks pretty dumb, tbh.
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;
If any row would do, try:
select max(user)
from table;
No where clause.
select name, price
from (
select name, price,
row_number() over (order by price) r
from items
)
where r between 1 and 5;
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.
More flexible than select max()
is:
select distinct first_row(column_x) over (order by column_y,column_z,...) from Table_A
Success story sharing
order by
.ORDER BY
is applied after theWHERE
.SELECT * FROM (SELECT user FROM Employees ORDER BY SALARY DESC) WHERE ROWNUM <= 10