Get max min per group in sequence




create table test   ( X CHAR(1), [Time] INT );

INSERT  INTO test
VALUES  ( 'A', 5 ),
        ( 'B', 6 ),
        ( 'B', 7 ),
        ( 'C', 8 ),
        ( 'C', 9 ),
        ( 'A', 10 ),
        ( 'B', 11 );


SELECT X, minTime=MIN([Time]), maxTime=MAX([Time])
FROM (
    SELECT X, [Time] ,[Time]-ROW_NUMBER() OVER (PARTITION BY X ORDER BY [Time]) rn
    FROM test) a
GROUP BY X, rn;

drop table test



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/923fa135-1f55-41e7-8eab-531ddeec3c78/get-max-min-per-group-in-sequence?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s