Select every second row mysql without auto_increment or data of table

Question

How can I select even numbered records in MySQL, without using any column id or auto_increment?

I have now this for the ODD rows:

SET @inc = 0;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;

And for even:

SET @inc = 0;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;

This works good. But on large tables it is too slow. Please suggest me any alternatives.

This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). — Kermit, Mar 22 '13 at 19:50
Also duplicate of [How do you select every n-th row from mysql](http://stackoverflow.com/questions/858746/how-do-you-select-every-n-th-row-from-mysql) and [select every other row in MySQL](http://stackoverflow.com/questions/14965747/select-every-other-row-in-mysql-without-depending-on-any-id) — Kermit, Mar 22 '13 at 19:50
@PolishPrince Huh. Wish I'd checked the comments before I answered... — Aaron Miller, Mar 22 '13 at 20:06
This is a duplicte, but I can't delete the question. Perfect answer: http://dba.stackexchange.com/questions/37374/select-every-second-row-without-auto-increment — Roman Holzner, Mar 29 '13 at 14:03

score 4 · Answer 1 · answered Mar 22 '13 at 20:02

4

maybe you look forsomething like this by MOD

try this

    select * from Table1 where mod(id, 2) <> '1'

DEMO HERE

if you want to output odd rows 1,3,5,... then use this

       select * from Table1 where mod(id, 2) <> '0'

answered Mar 22 '13 at 20:02

echo_Me

37,078
5
58
78

1

Note "without using any data of the table or some auto_increment" in the question, and see my answer for a working solution that meets the given requirement. – Aaron Miller Mar 22 '13 at 20:04

score 4 · Accepted Answer · answered Mar 22 '13 at 20:04

Assuming you're using a version of MySQL new enough to support subqueries -- and if not, what are you still doing on MySQL 3? -- here's an example of how it's done, using a temporary table with meaningless contents:

 mysql> create temporary table t (n text);
 Query OK, 0 rows affected (0.00 sec)

 mysql> insert into t (n) values ('a'), ('b'), ('c'), ('d'), ('e');
 Query OK, 5 rows affected (0.00 sec)
 Records: 5  Duplicates: 0  Warnings: 0

 mysql> select * from t;
 +------+
 | n    |
 +------+
 | a    | 
 | b    | 
 | c    | 
 | d    | 
 | e    | 
 +------+

 mysql> SET @i = 0; SELECT i, n FROM (SELECT @i := @i + 1 AS i, n FROM t) a WHERE MOD(a.i, 2) = 0;
 Query OK, 0 rows affected (0.00 sec)

 +------+------+
 | i    | n    |
 +------+------+
 |    2 | b    | 
 |    4 | d    | 
 +------+------+
 2 rows in set (0.00 sec)

 mysql> SET @i = 0; SELECT i, n FROM (SELECT @i := @i + 1 AS i, n FROM t) a WHERE MOD(a.i, 2) = 1;
 Query OK, 0 rows affected (0.00 sec)

 +------+------+
 | i    | n    |
 +------+------+
 |    1 | a    | 
 |    3 | c    | 
 |    5 | e    | 
 +------+------+
 2 rows in set (0.00 sec)

See how that works? You're setting session variable @i to zero, then, in the subquery, doing the equivalent of a preincrement on it to give you an index column along with your data rows. Then, in the outer query, you're selecting rows based on whether @i mod 2 is 0 (for even-numbered rows) or 1 (for odd-numbered rows).

I've selected the 'i' column in the outer query for purposes of illustration, but you don't need to do the same for the technique to work -- or, of course, if you're selecting * in the outer query, you can just ignore the 'i' column values in the result. (If you already have a column 'i' in your data table, just use a different alias in the subquery.)

The technique generalizes pretty well, too; for WHERE MOD(a.i, N) = 0, you get back every Nth row.

Select every second row mysql without auto_increment or data of table

2 Answers2

Linked