Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql group by only rows which are in sequence

Say I have the following table:

MyTable
---------
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | B |
| 5 | B |
| 6 | B |
| 7 | A |
| 8 | A |
---------

I need the sql query to output the following:

---------
| 3 | A |
| 3 | B |
| 2 | A |
---------

Basically I'm doing a group by but only for rows which are together in the sequence. Any ideas?

Note that the database is on sql server 2008. There is a post on this topic however it uses oracle's lag() function.

like image 442
Pavel Avatar asked Dec 01 '10 13:12

Pavel


People also ask

How do you sequence rows in SQL?

To number rows in a result set, you have to use an SQL window function called ROW_NUMBER() . This function assigns a sequential integer number to each result row. However, it can also be used to number records in different ways, such as by subsets.

Can we use GROUP BY without order?

group by does not order the data neccessarily. A DB is designed to grab the data as fast as possible and only sort if necessary. So add the order by if you need a guaranteed order. An efficient implementation of group by would perform the group-ing by sorting the data internally.

Can we SELECT column which is not part of GROUP BY?

The direct answer is that you can't. You must select either an aggregate or something that you are grouping by.

How do I SELECT the first row in a GROUP BY a group?

To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).


1 Answers

This is known as the "islands" problem. Using Itzik Ben Gan's approach:

;WITH YourTable AS
(
SELECT 1 AS N, 'A' AS C UNION ALL
SELECT 2 AS N, 'A' AS C UNION ALL
SELECT 3 AS N, 'A' AS C UNION ALL
SELECT 4 AS N, 'B' AS C UNION ALL
SELECT 5 AS N, 'B' AS C UNION ALL
SELECT 6 AS N, 'B' AS C UNION ALL
SELECT 7 AS N, 'A' AS C UNION ALL
SELECT 8 AS N, 'A' AS C
),
     T
     AS (SELECT N,
                C,
                DENSE_RANK() OVER (ORDER BY N) - 
                DENSE_RANK() OVER (PARTITION BY C ORDER BY N) AS Grp
         FROM   YourTable)
SELECT COUNT(*),
       C
FROM   T
GROUP  BY C,
          Grp 
ORDER BY MIN(N)
like image 181
Martin Smith Avatar answered Oct 02 '22 13:10

Martin Smith