I need to query a table to fetch data in groups of some size say N=5 but if the size is less than 5 then the last group should be ignored
i.e every 5 records should be grouped
==> i.e first 5 records form first group
==> next 5 records form second group
==> next 5 records form third group
....
What I have tried
I have tried the below query. It works but it fetches all the data.
My Requirement
I need to strip off the last group if it does not have 5 records there. in below query groupSize is the size of group
WITH T AS (
SELECT RANK() OVER (ORDER BY dateReceived ) Rank,
p.data
FROM parameterData p where parameterID=@parameterID
and isActive=1
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS GroupID, (Rank - 1) / @groupSize GroupSize, AVG(data) average, MIN(data) min,MAX(data) max,MAX(data)-MIN(data) range
FROM T
GROUP BY ((Rank - 1) / @groupSize);