Organizational Research By

Surprising Reserch Topic

how to select only 1 row from oracle sql


how to select only 1 row from oracle sql  using -'sql,oracle,oracle9i'

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

    

asked Sep 9, 2015 by WilliamsCrab
0 votes
6 views



Related Hot Questions

3 Answers

0 votes

"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;
answered Sep 9, 2015 by HowardFarrin
0 votes

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 Sep 9, 2015 by TrinaBrancantoq
0 votes

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.

answered Sep 9, 2015 by AdrDriskell

...