CODEWITHSUNDEEP
sqlsql-server
Karol Kolenda
Karol Kolenda

Reputation: 1690

Grouping records by equal number of records in SQL

Imagine we have a table with a single numeric column C1.

I would like to select max C1 from every 5 consecutive records (sorted by C1).

Example:

sorted C1: 1,3,4,8,9,10,11,13,16,18,21,34,66,67,68,80,82

C1 split into 5 record groups:

1,3,4,8,9 – 10,11,13,16,18 – 21,34,66,67,68 – 80,82

Final SQL result I'd like to achieve (max from every group):

9,18,68,82

Can it be done?

Upvotes: 1

Views: 404

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use row_number() to enumerate the rows and then arithmetic to aggregate:

select max(c1)
from (select t.*,
             row_number() over (order by c1) as seqnum
      from t
     ) t
group by ceiling( seqnum / 5.0 )
order by max(c1);

Here is a db<>fiddle.

Upvotes: 2

Related Questions