Search code examples
sqlsql-servergrouping

group rows in plain sql

I have a Table with columns Date and Number, like so:

date        Number 
1-1-2012    1
1-2-2012    1
1-3-2012    2
1-4-2012    1

I want to make a sql query that groups the rows with the same Number and take the minimum date. The grouping only may occur when the value iof Number is the same as previous / next row. So the rsult is

date        Number 
1-1-2012    1
1-3-2012    2
1-4-2012    1
Solution

  • try this:

    WITH   CTE AS(
       SELECT * ,ROW_NUMBER() OVER (ORDER BY [DATE] ) - 
                 ROW_NUMBER() OVER (PARTITION BY NUMBER ORDER BY [DATE] ) AS ROW_NUM
       FROM TABLE1)
SELECT NUMBER,MIN(DATE) AS DATE
FROM   CTE
GROUP BY ROW_NUM,NUMBER
ORDER BY DATE


    SQL fiddle demo