Home » SQL & PL/SQL » SQL & PL/SQL » Rownum with an order by clause
Rownum with an order by clause [message #35917] Tue, 23 October 2001 12:45 Go to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
Hello,

I need to retrieve top 100 rows from a table which has an order by clause and the result is wierd.

Eg.

select *
from MyTable
where UserId = 'Poonam'
and rownum <= 100
order by load_dt, user_loc;

-- Without the rownum, I get correct values, with rownum, its messed up !! Please advise.

From reviewing previous queries on this site, I understand that order by distorts the output of rownum ! If this is true can they never be used in conjunction?

Thanks in advance!!

----------------------------------------------------------------------
Re: Rownum with an order by clause [message #35918 is a reply to message #35917] Tue, 23 October 2001 13:16 Go to previous messageGo to next message
Suresh
Messages: 189
Registered: December 1998
Senior Member
Order by is the last statement in the sql parsing, first it retrives 100 rows and then it uses order by.

Work arounds
1) create a view with order by cluase and select rows from view with rownum clause

eg: select * from view1 where rownum<100

2) If these 2 columns are indexed you use can use index_asc hint as part of the sql.

----------------------------------------------------------------------
Re: Rownum with an order by clause [message #35920 is a reply to message #35917] Wed, 24 October 2001 01:22 Go to previous message
Milan Kumar Barui
Messages: 16
Registered: October 2001
Junior Member
Hi Poonam,
Rownum is number of rows selected by your query. In this case your query is :

select *
from MyTable
where UserId = 'Poonam';

your query will return all rows where userid ='POONAM' . Now when you give order by then it will order according to that. After this if your query is resuting okay and you want top 100 from this resultset, then you use :

select *
(select * from MyTable
where UserId = 'Poonam'
order by load_dt, user_loc)
where rownnum<=100;

because you are taking first 100 rows not from table, you have take from resultset returned by inner query.
Thanks
Milan



----------------------------------------------------------------------
Previous Topic: Using Return Cursor as out parameter in procedure
Next Topic: Re: Thanks,Orashark.But ??
Goto Forum:
  


Current Time: Fri Mar 29 07:50:04 CDT 2024