Home > MySQL, oracle > MySQL LIMIT 1,10 clause equivalent in ORACLE

MySQL LIMIT 1,10 clause equivalent in ORACLE

The other day a teammate has asked me an equivalent to MySQL limit clause.

He wants only the last 10 results of a table ordered by a date field. Using a subselect & rownum does not return the corerct results:


select * from (select rownum r, t.* from table t order by creation_date desc) where r < 10;

Because the rownum is assigned before the sort is applied to the resultset.

We’ve found this post http://forums.oracle.com/forums/thread.jspa?threadID=415724

with a nice solution:


select * from (select t.*, row_number() over (order by creation_date desc) r from table t) where r <= 10;

Thus, if you want the next 10 results,


select * from (select t.*, row_number() over (order by creation_date desc) r from table t) where r between 11 and 20;

Advertisements
Categories: MySQL, oracle Tags: , , ,
  1. No comments yet.
  1. No trackbacks 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: