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.
Author |
Topic |
berny978
Starting Member
1 Post |
Posted - 2013-04-29 : 17:23:00
|
Hi to all good people out there!I need a little help with one sql query. Suppose we have one table with data like this:Date ID-----------------4-1-2013 104-2-2013 104-3-2013 104-4-2013 104-5-2013 204-6-2013 204-7-2013 104-8-2013 104-9-2013 104-10-2013 30-----------------Now, I would like to get min and max dates for each ID, but like this:ID min max------------------------------10 4-1-2013 4-4-201320 4-5-2013 4-6-201310 4-7-2013 4-9-201330 4-10-2013 4-10-2013-------------------------------Is it possible somehow? Thanks in advance!B. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-29 : 17:30:11
|
[code];WITH cte AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Date) - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date ) AS G FROM YourTable)SELECT ID, MIN(Date) AS [Min], MAX(Date) AS [Max]FROM cteGROUP BY ID, GORDER BY [Min],ID[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-30 : 04:03:45
|
[code]select MIN([date]) AS [min],MAX([date]) AS [max],IDfrom TABLE touter apply (select MIN([Date]) AS Mindate FROM table WHERE ID <> t.ID AND [Date] > t.[Date])t1GROUP BY ID,Mindate[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|