0

How can I select every second and third record from mysql database?
Here is sql I am using

This sql needs to select every second record:

$sql_1 = "SELECT * FROM test_db ORDER BY id DESC";

And this one needs to select every third record:

$sql_2 = "SELECT * FROM test_db ORDER BY id DESC";

I will try to explain my question better.
http://pokit.org/get/img/f202a616d0b5e8fe4c6c5875b9a668be.jpg
This would be my mysql database. Database name is test_db. How I can display every second record from this database where LIMIT 2 and oreder by DESC? Order needs to be like this (44,15,6)

DirtyBit
  • 16,613
  • 4
  • 34
  • 55
Riki
  • 63
  • 8
  • possible 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) – u_mulder Sep 19 '15 at 09:44
  • Do you need third rank or third row..? – seahawk Sep 19 '15 at 09:47
  • I need every third record from mysql database. – Riki Sep 19 '15 at 09:49
  • Show example for every third row, second rows are so symmetrical. Do you mean 4->not, 6->not, 12->yeah, 15->not, 23->not, 44->yeah, 56->not and then order 44, 12? Or is it 56->no, 44->no, 23->yeah, 15->no, 12->no, 6->yeah? – flaschenpost Sep 19 '15 at 10:56
  • So where are we with this question? – DirtyBit Sep 19 '15 at 11:50
  • I really don't know how to explain more this question, I don't know how to set question ... – Riki Sep 19 '15 at 12:05

2 Answers2

1

You should define "every second record" more clearly. Every second is only reasonable if there is an order.

Do you mean the id?

Then it would be

select * from test_db where id % 2 == 0; // or id % 2 == 1

should it be every second in some other order?

Then I would go for (EDIT: order by needs to be inside subselect)

set @counter:=0;
select * from 
( select @counter:=@counter+1 as cnt, t.*
  from test_db as t
  order by inserted desc) as isel 
where cnt % 2 = 0;

Or if you want to sort by ascending id to find first row to throw away then use

set @counter:=0;
select * from 
( select @counter:=@counter+1 as cnt, t.*
  from test_db as t
  order by id) as isel 
where cnt % 2 = 0
order by id desc;

Or if you want to sort by descending id to find first row to throw away then use

set @counter:=0;
select * from 
( select @counter:=@counter+1 as cnt, t.*
  from test_db as t
  order by id desc) as isel 
where cnt % 2 = 0
;

I hope you get the idea.

flaschenpost
  • 2,205
  • 1
  • 14
  • 29
  • @Riki What is the "first" row? MySQL has a kind of "natural" order, do you mean this order? Or is the lowest id the "first" row to throw away? – flaschenpost Sep 19 '15 at 10:51
  • First record needs to have highest 'id', following records will have smaller id – Riki Sep 19 '15 at 12:04
  • So my sql should looks like this? `$sql = "set @counter:=0; select * from ( select @counter:=@counter+1 as cnt, t.* from test_db as t order by id desc) as isel where cnt % 2 = 0 ;"; ` – Riki Sep 19 '15 at 16:41
  • When i run that i got an error `Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\prodaja\index.php on line 85` – Riki Sep 19 '15 at 16:42
  • The first query (`set @counter:=0`) is like an update, so not `select`- like, I think you need a different `mysqli_` method. – flaschenpost Sep 19 '15 at 17:19
  • Can you write me code because I am afraid i do not understeant. – Riki Sep 19 '15 at 20:27
0

This could be done using mod.

For every 2, 4, 6, record:

SELECT * FROM test_db WHERE mod(id, 2) <> '1';

And for odd number of rows (1,3,5,7) to be fetched:

SELECT * FROM test_db WHERE mod(id, 2) <> '0';

1st SQL Fiddle

EDIT:

As per the changes you mentioned in the comment, this should work:

SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, id
    FROM (
        SELECT @row :=0) r, test_db
    ) ranked
WHERE rownum %4 =1

2nd SQL Fiddle

Second EDIT:

After reading your edited question, a few changes made and this should be fine:

SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, id
    FROM (
        SELECT @row :=0) r, test_db
    ) ranked
WHERE rownum %2 = 0 ORDER BY id DESC LIMIT 5

3rd SQL Fiddle

DirtyBit
  • 16,613
  • 4
  • 34
  • 55