1

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);
  • Can you create a dbfiddle.uk with some sample data that illustrates the problem? – Lennart Jul 3 at 14:15
  • I have added dbfiddle.uk/… Totally there are 6 rows here SO we can form only 1 complete group. Remaing 2 rows should not be considered Here if groupSize = 4 then only 1 rows should come. But the last row is fetched using last 2 rows which is less than the group size(4) – jAntoni Jul 3 at 14:31
1
  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;
| improve this answer | |
  • anyone got a link to how to format code ? – Mo64 Jul 3 at 14:19
  • Thanks its working for me. dbfiddle.uk/… – jAntoni Jul 3 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.