sqloraclegreatest-n-per-group

How to get the recent 10 users by date


I have a table named users with columns username and created_date. I want to get 10 recently created users by using query.

Query:

select to_char(created_date, 'DD-MM-YYYY')
  from users
 order by created_date desc;

This returns users created in descending order. But I want to get first 10 records.


Solution

  • Try to use subquery and ROWNUM SELECT * FROM (SELECT * FROM users ORDER BY created_date DESC ) WHERE ROWNUM <= 10