Skip to content

Return Top Rows from Result set

Purpose:  Return only top 5 rows from the result that is in ascending or descending order

what is not working? : i am trying to use ROWNUM but it does not return the result that i wanted after ordering.

Solution: in 12c onwards they have fetch first n rows only feature

Example: return top 5 rows for the most recent login and logoff

Right SQL

select userid,action#,ntimestamp# from sys.aud$ where USERID = ‘MYNAME’ AND ACTION# in (100,101) order by ntimestamp# desc FETCH FIRST 5 ROWS ONLY;

Result: it returns first five rows in desceding order that is something what i am looking for

MYNAME 100 5/2/2019 9:45:18.764798 PM
MYNAME 100 4/30/2019 3:20:22.897547 PM
MYNAME 100 4/26/2019 6:32:27.668384 PM
MYNAME 100 4/25/2019 7:49:26.328111 PM
MYNAME 100 4/25/2019 3:34:09.956507 PM

Wrong SQL : it does not return what i want.

select userid,action#,ntimestamp# from sys.aud$ where USERID = ‘MYNAME’ AND ACTION# in (100,101) and rownum <=5 order by ntimestamp# desc;

MYNAME 101 6/25/2018 9:38:59.119950 PM
MYNAME 100 6/25/2018 7:04:09.036674 PM
MYNAME 100 6/25/2018 7:03:59.721711 PM
MYNAME 101 6/25/2018 6:34:55.063466 PM
MYNAME 100 6/25/2018 6:19:33.075436 PM

Alternative in 12c below

Here is what works in 11g using subquery

select * from
(select userid,action#,ntimestamp# from sys.aud$ where USERID = ‘MYNAME’ AND ACTION# in (100,101) order by ntimestamp# desc) where rownum <=5 ;

MYNAME 100 5/2/2019 9:45:18.764798 PM
MYNAME 100 4/30/2019 3:20:22.897547 PM
MYNAME 100 4/26/2019 6:32:27.668384 PM
MYNAME 100 4/25/2019 7:49:26.328111 PM
MYNAME 100 4/25/2019 3:34:09.956507 PM

 

 

 

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: