SQL SERVER 2008 – How to select EVERY Nth row from a table

One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

Method 1: Using a subquery

SELECT *         FROM   emp         WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)                              FROM   emp)

Method 2: Use dynamic views

SELECT *         FROM   ( SELECT rownum rn, empno, ename                  FROM emp                ) temp         WHERE  MOD(temp.ROWNUM,4) = 0

Method 3: Using GROUP BY and HAVING

SELECT rownum, f1         FROM t1         GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n

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

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

Advertisements

4 thoughts on “SQL SERVER 2008 – How to select EVERY Nth row from a table

  1. Hi I am just learning SQL and I would like share something which I learned. In my class I was asked to find out the 1st row and the last row of the table. For which we all broke our heads to find out. but we we were able to do it in a simpler way.

    My Table Name is EMPLOYEES

    SELECT * FROM (SELECT ROWNUM AS RN, EMPLOYEES.* FROM EMPLOYEES) WHERE RN=1 OR RN=(SELECT MAX(ROWNUM) FROM EMPLOYEES);

    OR

    SELECT * FROM (SELECT ROWNUM AS RN, EMPLOYEES.* FROM EMPLOYEES) WHERE RN=(SELECT MIN(ROWNUM) FROM EMPLOYEES) OR RN=(SELECT MAX(ROWNUM) FROM EMPLOYEES);

    If you specifically want to pick only the 1st row then use the below code:

    SELECT ROWNUM, EMPLOYEES.* FROM EMPLOYEES WHERE ROWNUM=1;

    ROWNUM=1 will work only for the 1st row and for no other rows.

    So if you want rows 1 to 5 then use the below code

    SELECT ROWNUM, EMPLOYEES.* FROM EMPLOYEES WHERE ROWNUM<=5;

    same way for rows 1 to 10 replace the ROWNUM in the above code for the where clause as

    ROWNUM<=10

    So basically it is ROWNUM<=N to return that many rows of a table.

    Hope it helps.

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