SQL Server – Grouping by N Records and Removing Incomplete Groups

sql servert-sql

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);

Best Answer

  WITH T AS 
(
  SELECT 
    RANK() OVER (ORDER BY dateReceived ) Rank,
    p.data
  FROM parameterData p where parameterID=@parameterID
    and dateReceived between @fromDate and @toDate
    and recipe = @recipe
    and isActive=1
)
SELECT 
  ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS GroupID, 
  (Rank - 1) / @groupSize as GroupSize, 
  AVG(data) as average, 
  MIN(data) as min,
  MAX(data) as max,
  MAX(data)-MIN(data) as range, 
  count(*) as cnt
FROM T
GROUP BY ((Rank - 1) / @groupSize)
HAVING count(*) = 5;
Related Question