SQL SERVER 2008 – How To retrieve only the Nth row from a table

1)

SELECT * FROM t1 a

WHERE  n = (SELECT COUNT(rowid)

FROM t1 b

WHERE a.rowid >= b.rowid);

2)

SELECT * FROM (

SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )

WHERE  RN = 100;

Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.

3)

SELECT f1 FROM t1

WHERE  rowid = (

SELECT rowid FROM t1

WHERE  rownum <= 10

MINUS

SELECT rowid FROM t1

WHERE  rownum < 10);

SELECT rownum,empno FROM scott.emp a

GROUP BY rownum,empno HAVING rownum = 4;

Alternatively…

SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN

(SELECT rowid FROM emp WHERE rownum < 10);

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

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