CODEWITHSUNDEEP
mysqlselect
madkitty
madkitty

Reputation: 1675

How to select rows every record+100 value?

I have a table of integer, with two columns.

Let's say MySQL starts to read at col1, row1, the values are 47 and 99. What I want MySQL to do, is to select data from col1 every values+100. So here col1 starts at 47, MySQL should select 47 147 247

FYI : this table has about 500.000 rows. .. According to stats, this should return about 50,000 rows. So I need a super-fast query.

I had several attempt that didn't work out, with smth like that:

    SELECT * 
      FROM ( 
           SELECT 
                @row := @row +100 AS rownum, col1
           FROM (SELECT @row :=0) r, my_table 
           ) ranked 
    WHERE rownum % [n] = 1

Upvotes: 1

Views: 313

Answers (1)

Haim Evgi
Haim Evgi

Reputation: 125486

do something like

SELECT * FROM mytable WHERE col1 % 100 = 
   (select col1 % 100 from mytable order by col1 limit 1)

Upvotes: 2

Related Questions