question

sunny avatar image
sunny asked

Grouping based on number of records

I have a table with the following structure

DateTime | Value
2010-01-01 00:08:25.000 | 5
2010-01-01 00:08:55.000 | 6
2010-01-01 00:06:55.000 | 8
2010-01-01 00:07:25.000 | 3
2010-01-01 00:07:55.000 | 7
2010-01-01 00:09:25.000 | 2
2010-01-01 00:13:25.000 | 3
2010-01-01 00:13:55.000 | 4
2010-01-01 00:14:25.000 | 2
2010-01-01 00:14:55.000 | 3

I want to now group the data based on 2 records each and have the min and max value. The output need to appear like this (for the DateTime, i only need to consider the second value)

Output Required

DateTime | Min |Max
2010-01-01 00:08:55.000 | 5 | 6
2010-01-01 00:07:25.000 | 3 | 8
2010-01-01 00:09:25.000 | 2 | 7
2010-01-01 00:13:55.000 | 3 | 4
2010-01-01 00:14:55.000 | 2 | 3

Can this be achieved via a single SQL Statement?

Thanks in advance for the help?

sql-server-2005
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
What's the differentiator between two records? How do you define the second value or are you ordering by the datetime & then taking every other one?
0 Likes 0 ·
CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Not sure I follow. The two sets of data do not match - thus making this very difficult to understand the real requirements. Please clarify.
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
CREATE TABLE #tab(ID int identity,[DateTime] datetime, Value int)
INSERT #tab
SELECT '2010-01-01 00:08:25.000', 5 UNION ALL
SELECT '2010-01-01 00:08:55.000' , 6 UNION ALL
SELECT '2010-01-01 00:06:55.000' , 8 UNION ALL
SELECT '2010-01-01 00:07:25.000' , 3 UNION ALL
SELECT '2010-01-01 00:07:55.000' , 7 UNION ALL
SELECT '2010-01-01 00:09:25.000' , 2 UNION ALL
SELECT '2010-01-01 00:13:25.000' , 3 UNION ALL
SELECT '2010-01-01 00:13:55.000' , 4 UNION ALL
SELECT '2010-01-01 00:14:25.000' , 2 UNION ALL
SELECT '2010-01-01 00:14:55.000' , 3 


SELECT MAX([DateTime])[DateTime]
      ,MIN(Value)[Min]
      ,MAX(Value)[Max] 
FROM (SELECT [DateTime]
            ,[Value]
            ,NTILE((SELECT COUNT(*)/2 FROM #tab)) OVER (ORDER BY ID) [Partition]
FROM #tab) d
GROUP BY [Partition]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

Use (ROW_NUMBER()+1)/2 to make a PairNumber.

Select 
   Max([DateTime]),
   Min(Value), Max(Value)
FROM
  (select *,
     (Row_number() over (order by [DateTime]) +1)/2
       as PairNum
   from YourTable
  ) t
GROUP BY PairNum;   
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder commented ·
Thanks Rob, I've never used NTILE in an answer so I thought I'd explore that...The data isn't ordered by date, I think that is why the other viewers didn't post an answer...data + question is a little inconsistent
0 Likes 0 ·
Rob Farley avatar image Rob Farley commented ·
My one wasn't suggesting NTILE, that was the other one. Work out what the order should be, and then use that in the OVER clause.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.