CODEWITHSUNDEEP
sqloracle-database
FJam
FJam

Reputation: 756

Get first row in Oracle Sql

I am trying to get only the first row from this query. The query does not return the top row. Here is the query.

SELECT DISTINCT name, age
FROM donates, persons
WHERE name = donor 
AND name IN (SELECT receiver FROM donates)
AND ROWNUM <= 1
ORDER BY age DESC;

When I run the query it returns Chirs |35. Without the ROWNUM <=1 this is what the table looks like.

NAME   |  AGE
-------------
george |  62
Chris  |  35
zara   |  24

Upvotes: 1

Views: 12687

Answers (3)

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

Try to use ROWID rather than ROWNUM like this:

SELECT * FROM TABLE_NAME WHERE ROWID = (SELECT MIN(ROWID) FROM TABLE_NAME)

Upvotes: 2

Marcelo Rossi
Marcelo Rossi

Reputation: 384

You are trying to get the one with minimal age, so I suggest you to use the Min sql built-in function:

SELECT name, Min(age)
FROM donates, persons
WHERE name = donor 
AND name IN (SELECT receiver FROM donates)

It is a good practice to avoid rownum. And here you get a more semantic and efficient query.

Hope it helps, Regards

Upvotes: 0

Olly W
Olly W

Reputation: 377

I think the best way to get this is to use a subquery as the rownum is being looked at before the order by so:

select * from (
SELECT DISTINCT name, age
 FROM donates, persons
 WHERE name = donor 
 AND name IN (SELECT receiver FROM donates)
 ORDER BY age DESC
) where ROWNUM <= 1;

For a longer read look at http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Upvotes: 3

Related Questions