Clever Geek Handbook

Select every other row in MySQL without any identifier?

Given the following table, which doesn't have a primary key, can any other row be selected?

col1      col2
 2         a
 1         b
 3         c
 12        g

first select: 2, 3

The second choice is to find: 1, 12

possibly?

+3


source to share


6 answers


In a unique MySQL version:

select  *
from    (
        select  *
        ,       @rn := @rn + 1 as rn
        from    Table1
        join    (select @rn := 0) i
        ) s
where   rn mod 2 = 0 -- Use = 1 for the other set


Example in SQL Fiddle.

+4


source


This should work for MySQL:

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


In this case,% is used, which is the MOD operator.

And here is a sample script: http://sqlfiddle.com/#!2/cd31b/2

+1


source


Try it. I adapted it from the answer below. I tested it on SQLFiddle and it seems to work.

http://sqlfiddle.com/#!2/0bccf/28
http://sqlfiddle.com/#!2/0bccf/29

Odd lines:

SELECT x.*
FROM (
     SELECT @rownum:=@rownum+1 rownum, t.*
     FROM (SELECT @rownum:=0) r, table t
) x
WHERE MOD(x.rownum, 2) = 1

Even lines:

SELECT x.*
FROM (
     SELECT @rownum:=@rownum+1 rownum, t.*
     FROM (SELECT @rownum:=0) r, table t
) x
WHERE MOD(x.rownum, 2) = 0

Adapted from: MySQL Row Number

+1


source


Consider this related answer: How to show only even or odd rows in SQL Server 2008?

It seems that you are doing exactly what you want. I think this works in MySql, but I'm not sure.

0


source


doesn't work, but still remains.

  SELECT @row: = @row + 1 row AS, ... FROM yourtable, (SELECT @row: = -1) as foo ... HAVING row% 2 = 0 Div>
0


source


yes it is possible to use a temporary variable

Example:

set @a := 0;
select * from car_m_city  WHERE mod((@a:=@a+1), 2) = 1

Explanation:

here in sql we are declaring temp @a ( set @a := 0;

) variable . (@a:=@a+1)

now @a

increment on 1.jsut as a simple way to check for odd or even

mod((@a:=@a+1), 2) = 1

for odd data

mod((@a:=@a+1), 2) = 0

for even data

0


source






More articles:


All Articles