1

I have a query that returns a large (10000+ rows) dataset. I want to order by date desc, and display the first 40 results. Is there a way to run a query like this that only retrieves those 40 results without retrieving all 10000 first?

I have something like this:

select rownum, date, * from table order by date desc

This selects all the data and orders it by date, but the rownum is not in order so it is useless for selecting only the first 40.

ROW_NUMBER() over (ORDER BY date desc) AS rowNumber

^ Will display a rownumber in order, but I can't use it in a where clause because it is a window function. I could run this:

select * from (select ROW_NUMBER() over (ORDER BY date desc) AS rowNumber, rownum, * from table order by date desc) where rowNumber between pageStart and pageEnd

but this is selecting all 10000 rows. How can I do this efficiently?

Improve this question
2
  • which database are you using? Commented Jul 9, 2015 at 19:47
  • Is the value of rowNumber between pageStart and pageEnd? Commented Jul 9, 2015 at 19:55

3 Answers 3

Reset to default
5 
SELECT * FROM (SELECT * FROM table ORDER BY date DESC) WHERE rownum <= 40

will return the first 40 rows ordered by date. If there is an index on date that can be used to find these rows, and assuming statistics are up to date, Oracle should choose to use that index to identify the 40 rows that you want and then do 40 single-row lookups against the table to retrieve the rest of the data. You could throw a /*+ first_rows(40) */ hint into the inner query if you want though that shouldn't have any effect.

For a more general discussion on pagination queries and Top N queries, here's a nice discussion from Tom Kyte and a much longer AskTom discussion.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Oracle 12c has introduced a row limiting clause:

SELECT * FROM table ORDER BY "date" DESC FETCH FIRST 40 ROWS ONLY;

In earlier versions you can do:

SELECT * FROM ( SELECT * FROM table ORDER BY "date" DESC ) WHERE ROWNUM <= 40;

or

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( ORDER BY "date" DESC ) AS RN FROM table ) WHERE RN <= 40;

or

SELECT * FROM TEST WHERE ROWID IN ( SELECT ROWID FROM ( SELECT "Date" FROM TEST ORDER BY "Date" DESC ) WHERE ROWNUM <= 40 );

Whatever you do, the database will need to look through all the values in the date column to find the 40 first items.

Improve this answer

Comments

-1

You don't need a window function. See

http://www.techonthenet.com/oracle/questions/top_records.php

for an answer to your problem.

Improve this answer

1 Comment

Please provide an answer, not a link

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.