How to select only 1 row from oracle sql?

126

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 question is tagged with sql oracle oracle9i

~ Asked on 2012-01-19 00:22:11

The Best Answer is


182

You use ROWNUM.

ie.

SELECT user FROM Dual WHERE ROWNUM = 1

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

~ Answered on 2012-01-19 00:25:51


55

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.

~ Answered on 2014-07-30 08:42:34


Most Viewed Questions: