Rownum with an order by clause [message #35917] |
Tue, 23 October 2001 12:45 |
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 |
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 |
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
----------------------------------------------------------------------
|
|
|