CODEWITHSUNDEEP
mysqlsql
Birjolaxew
Birjolaxew

Reputation: 817

MySQL: Select first row with value in interval

With the following table:

CREATE TABLE table1 (`id` INT, `num` INT);
INSERT INTO table1 (`id`, `num`) VALUES
     (1, 1),
     (1, 5),
     (1, 7),
     (1, 12),
     (1, 22),
     (1, 23),
     (1, 24),
     (2, 1),
     (2, 6);

How do I select a row for each num interval of 5 (ie. select the first row for [0,5), the first for [5,10), the first for [10,15), etc.), with a given id? Is this possible with a MySQL query, or must I process it in a programming language later?

For reference, the output I'd want for id=1:

(1, 1), (1,5), (1,12), (1,22)

Upvotes: 1

Views: 673

Answers (1)

Abdullah Nehir
Abdullah Nehir

Reputation: 1047

Here is a short query:

select min(num), ceiling((num + 1)/5)
from table1
where id = 1
group by ceiling((num + 1)/5);

Upvotes: 2

Related Questions