CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10g
Neylena
Neylena

Reputation: 23

fetching upto a limited condition in oracle query

I need to alter the query below, to fetch the first 5 most recent users, instead of just the most recent single user. But as sql do not allow LIMIT like it can be done in mysql, i have prepared this:

SELECT * FROM users WHERE joined in (select max(joined) from users)

This will fetch me most recent user but can not understand how i can extend it to 5 most recent users.

Upvotes: 2

Views: 52

Answers (2)

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

SOmething like this must work for you:

      SELECT * FROM (select * from users order by joined DESC) u where rownum<=5

If you select from already ordered data, rownum, will correspond to the latest date.

Upvotes: 3

potashin
potashin

Reputation: 44581

SELECT m.* 
FROM (SELECT u.*
           , rank() OVER (ORDER BY u.joined) r 
      FROM users u) m 
WHERE r BETWEEN 0 AND 5 
ORDER BY m.joined

Upvotes: 1

Related Questions