sql group by only rows which are in sequence

by

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)

More Related Contents:

  1. GROUP BY to combine/concat a column [duplicate]
  2. Detect consecutive dates ranges using SQL
  3. How to do a case sensitive GROUP BY?
  4. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  5. Unpivot with column name
  6. How can I delete using INNER JOIN with SQL Server?
  7. Is there a way to access the “previous row” value in a SELECT statement?
  8. What this query does to create comma delimited list SQL Server?
  9. SQL Server – Return value after INSERT
  10. Convert varchar into datetime in SQL Server
  11. sql query to return differences between two tables
  12. Check if a row exists, otherwise insert
  13. SQL Server : dynamic pivot over 5 columns
  14. How to select the last record of a table in SQL?
  15. How do I group on continuous ranges
  16. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  17. How to write UPDATE SQL with Table alias in SQL Server 2008?
  18. SQL Server 2008- Get table constraints
  19. Restrict an SQL Server connection to a specific IP address
  20. Execute Dynamic Query with go in sql
  21. TransactSQL to run another TransactSQL script
  22. How can I get a list of element names from an XML value in SQL Server
  23. Why are logical reads for windowed aggregate functions so high?
  24. How can I group time by hour or by 10 minutes?
  25. SQL Server equivalent to MySQL enum data type?
  26. Improve SQL Server query performance on large tables
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. Cumulative Total in MS Sql server [duplicate]
  29. SQL query to group by day
  30. Editing Record issues in Access / SQL (Write Conflict)

Leave a Comment