Select only even/odd rows in MySQL

Question

I'm trying to select all even or odd rows from a table in MySQL without using the ID field. I tried this, but I suppose that it doesn't work since it's based on SQL Server: how to show only even or odd rows in sql server 2008?

Thank you all in advance.

There is no such thing as an "even" or "odd" row in MySQL. Tables represent unordered sets. You need a column that specifies the ordering of the rows. — Gordon Linoff, Sep 23 '14 at 11:30
[http://stackoverflow.com/questions/15578727/select-every-second-row-mysql-without-auto-increment-or-data-of-table] — Anptk, Sep 23 '14 at 11:40

score 11 · Accepted Answer · answered Sep 23 '14 at 11:33

11

Assuming you have a column that specifies the ordering of the table, then you can use variables to do what you want:

select t.*
from (select t.*, (@rn := @rn + 1) as seqnum
      from table t cross join
           (select @rn := 0) vars
      order by col
     ) t
where mod(seqnum, 2) = 0;

answered Sep 23 '14 at 11:33

Gordon Linoff

1,242,037
58
646
786

1

It works, thanks! Do I ask too much if I ask you to explain it? I don't understand the "@rn" and the "VARS" meaning. – AleVale94 Sep 23 '14 at 11:44
2

`vars` is a table alias and `@rn` is a variable in MySQL. You can read about variables here: http://dev.mysql.com/doc/refman/5.7/en/user-variables.html. – Gordon Linoff Sep 23 '14 at 13:39
I have a question: You are using PL/SQL? – Vishwas Atrey Aug 31 '19 at 12:13

score 5 · Answer 2 · answered Sep 23 '14 at 11:34

5

Try to use this:-

SELECT cols
FROM (
      SELECT cols, @rowNumber := @rowNumber+ 1 rn
      FROM YourTable
      JOIN (SELECT @rowNumber:= 0) r
     ) t 
WHERE rn % 2 = 1;

answered Sep 23 '14 at 11:34

Ankit Bajpai

13,128
4
25
40

Select only even/odd rows in MySQL

2 Answers2

Linked