Grouping items - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping items

Author

Topic

CJM
Starting Member

2 Posts

Posted - 2010-03-23 : 13:03:20

I have data that I need to group by an ID and get the max and min time for each group. The problem is that I have an ID for 5 minutes that I need the min/max on then another ID for 2 minutes then back to the same ID as the first one. I need a seperate min/max on this ID again - I want the Min/Max for each continuous set of IDs when sorted by time. I can do it with a cursor but I have a large number or rows and would like to find a cursorless solution.

Any Ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:10:05

seems like this


SELECT t.ID,MIN(t.time),MAX(t.time)
FROM Yourtable t
OUTER APPLY (SELECT MIN(time) AS MinTime
             FROM Table
             WHERE ID <> t.ID
             AND time > t.time)t1
GROUP BY t.ID,t1.MinTime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CJM
Starting Member

2 Posts

Posted - 2010-03-23 : 13:40:11

Very cool - I have not used APPLY much and had not thought of it.

Thanks

quote:
Originally posted by visakh16

seems like this

SELECT t.ID,MIN(t.time),MAX(t.time)
FROM Yourtable t
OUTER APPLY (SELECT MIN(time) AS MinTime
FROM Table
WHERE ID <> t.ID
AND time > t.time)t1
GROUP BY t.ID,t1.MinTime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 13:57:04

quote:
Originally posted by CJM

Very cool - I have not used APPLY much and had not thought of it.

Thanks

quote:
Originally posted by visakh16

seems like this

SELECT t.ID,MIN(t.time),MAX(t.time)
FROM Yourtable t
OUTER APPLY (SELECT MIN(time) AS MinTime
FROM Table
WHERE ID <> t.ID
AND time > t.time)t1
GROUP BY t.ID,t1.MinTime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok. In that case read about it in books online and see what all you can do with it below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources