Considering following table that doesn’t have any primary key, can I select every other row?
col1 col2 2 a 1 b 3 c 12 g
first select must find: 2, 3
second select must find: 1, 12
is that possible?
Here is Solutions:
We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.
In unique MySQL fashion:
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
Try this. I’ve adapted it from the answer linked below.
I tested it on SQLFiddle and it appears to work.
SELECT x.* FROM ( SELECT @rownum:[email protected]+1 rownum, t.* FROM (SELECT @rownum:=0) r, table t ) x WHERE MOD(x.rownum, 2) = 1
SELECT x.* FROM ( SELECT @rownum:[email protected]+1 rownum, t.* FROM (SELECT @rownum:=0) r, table t ) x WHERE MOD(x.rownum, 2) = 0
MySQL row number
yes possible using temp variable
set @a := 0; select * from car_m_city WHERE mod((@a:[email protected]+1), 2) = 1
here in sql we declare @a(
set @a := 0;) temp variable.
(@a:[email protected]+1) now
@a increment by 1.jsut like simple way to check odd or even
mod((@a:[email protected]+1), 2) = 1 for odd data
mod((@a:[email protected]+1), 2) = 0 for even data
This works for me.
SET @row_number = 0; select* from ( SELECT (@row_number:[email protected]_number + 1) AS num, col1,col2 FROM TABLE1 ) as t WHERE num%2=0
You can use mod 1 for odd or mod 0 for even rows
This should work for MySQL:
SELECT col1, col2 FROM ( SELECT col1, col2, @rowNumber:[email protected]+ 1 rn FROM YourTable JOIN (SELECT @rowNumber:= 0) r ) t WHERE rn % 2 = 1
This uses % which is the MOD operator.
And here is the sample fiddle: http://sqlfiddle.com/#!2/cd31b/2
Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂