SQL SERVER 2008 – How to retrieve only rows X to Y from a table

1)

SELECT * FROM (

SELECT ename, rownum rn

FROM emp WHERE rownum < 101

) WHERE  RN between 91 and 100 ;

Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).

2)

SELECT rownum, f1 FROM t1         GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:

SELECT *         FROM   tableX         WHERE  rowid in (            SELECT rowid FROM tableX            WHERE rownum <= 7           MINUS            SELECT rowid FROM tableX            WHERE rownum < 5);

3) “this one was faster for me and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied.”

SELECT * FROM (SELECT a.*, rownum RN                  FROM (SELECT *                           FROM t1 ORDER BY key_column) a                  WHERE rownum <=7)         WHERE rn >=5

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

One thought on “SQL SERVER 2008 – How to retrieve only rows X to Y from a table

  1. as per my knowledge, there is no pseudo column called rownum in Sql server 2008 so I think this code wont work, hope this might be work for Oracle but not for Sql server

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