RowNum and Order by 2

[NuniPR]

Hi Guys!

I have a table that has 47,000,000 records. I need to get the first 1,000 rows in descending last_modified_date order.

I get as far as this:

select rownum, location_id, last_modified_userid, 
last_modified_date from location 
where rownum <= 1000 
order by last_modified_date desc

When I do this, what I get is the first 1,000 rows ordered. Does any of you guys have the correct syntax to get the table sorted and those first 1,000 records of the new order?

Thanks a bunch, guys!


Nunina
IT Manager
San Juan, PR

http://www.endopuertorico.org

 

[LKBrwnDBA]

Try this:

SELECT *
  FROM (SELECT location_id, last_modified_userid, last_modified_date,
               ROW_NUMBER () OVER (ORDER BY last_modified_date DESC) rn
          FROM LOCATION)
 WHERE rn <= 1000;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

Nuni,

the illustrious Mr Thomas Kyte has this already done and dusted.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064

refers.

Regards

T