Group by every N records in T-SQL

Question

I have some performance test results on the database, and what I want to do is to group every 1000 records (previously sorted in ascending order by date) and then aggregate results with AVG.

I'm actually looking for a standard SQL solution, however any T-SQL specific results are also appreciated.

The query looks like this:

SELECT TestId,Throughput  FROM dbo.Results ORDER BY id 

Answer 1

WITH T AS (   SELECT RANK() OVER (ORDER BY ID) Rank,     P.Field1, P.Field2, P.Value1, ...   FROM P ) SELECT (Rank - 1) / 1000 GroupID, AVG(...) FROM T GROUP BY ((Rank - 1) / 1000) ; 

Something like that should get you started. If you can provide your actual schema I can update as appropriate.

Answer 2

Give the answer to Yuck. I only post as an answer so I could include a code block. I did a count test to see if it was grouping by 1000 and the first set was 999. This produced set sizes of 1,000. Great query Yuck.

    WITH T AS (     SELECT RANK() OVER (ORDER BY sID) Rank, sID      FROM docSVsys     )     SELECT (Rank-1) / 1000 GroupID, count(sID)     FROM T     GROUP BY ((Rank-1) / 1000)     order by GroupID