SQL: How to select 1st, 3rd, 11th and nth row from a table?

Question

How to select 1st, 3rd, 11th and nth row from a table?

Answer 1

First of all, you should never ever rely on the order of the rows in your database. Rows have no natural ordering. However, you can add an attribute (unsigned int with auto increment, for instance) which indicates the order of the rows. Be sure that whenever the table is edited, the field is updated accordingly.

You can now select the first, third and eleventh row

SELECT * FROM table t
WHERE t.order IN (1, 3, 11)
ORDER BY t.order ASC;

Answer 2

For MySQL

SET @rows_count = NULL;

select col_list,rn from 
(
    select col_list, @rows_count := IFNULL(@rows_count, 0) + 1 as rn
    from table_name
    order by col_list
    limit 11) top_n
WHERE rn IN (1,3,11)

Answer 3

If there is a primary key defined for the table that is an integer based data type--both MySQL and SQLite have auto_increment for example--then you can use:

SELECT t.*
  FROM TABLE t
 WHERE t.id IN (1,3, 11)

...where id is the auto_increment column.

There's very little detail to go on, but MySQL and SQLite do not have analytic query support, making queries rather complicated:

SELECT y.*
  FROM (SELECT t.*,
                        (SELECT COUNT(*)
                            FROM TABLE x
                           WHERE x.col <= t.col) AS rank
               FROM TABLE t) y
 WHERE y.rank IN (1, 3, 11)

Answer 4

try this way

Select * 
From ( Select Row_Number() OVER (Order by empno) rno, 
       e.* From scott.emp e )
Where rno in (1, 3, 11)