A simple but challenging SQL Question, at least I couldn't find a way out except doing it externally (c#)

algorithm, sql, sql-server, t-sql

Solution

Assuming the data can be sorted on your `Column`, the plan is to generate a `row number` for each row and do a `group by` to retrieve your data points.

SQL Server 2000

DECLARE @YourTable TABLE (Col VARCHAR(1))
CREATE TABLE #TempTable (ID INTEGER IDENTITY(1, 1), Col VARCHAR(1))

SET IDENTITY_INSERT #TempTable OFF
INSERT INTO @YourTable (Col) VALUES ('A')
INSERT INTO @YourTable (Col) VALUES ('A')
INSERT INTO @YourTable (Col) VALUES ('A')
INSERT INTO @YourTable (Col) VALUES ('B')
INSERT INTO @YourTable (Col) VALUES ('B')
INSERT INTO @YourTable (Col) VALUES ('B')
INSERT INTO @YourTable (Col) VALUES ('B')
INSERT INTO @YourTable (Col) VALUES ('C')
INSERT INTO @YourTable (Col) VALUES ('D')
INSERT INTO @YourTable (Col) VALUES ('D')
INSERT INTO @YourTable (Col) VALUES ('E')

INSERT INTO #TempTable
SELECT      *
FROM        @YourTable
ORDER BY    Col

SELECT    MAX(ID)
FROM      #TempTable
GROUP BY  Col

DROP TABLE #TempTable

SQL Server 2005

DECLARE @Table TABLE (Col VARCHAR(1))

INSERT INTO @Table VALUES ('A')
INSERT INTO @Table VALUES ('A')
INSERT INTO @Table VALUES ('A')
INSERT INTO @Table VALUES ('B')
INSERT INTO @Table VALUES ('B')
INSERT INTO @Table VALUES ('B')
INSERT INTO @Table VALUES ('B')
INSERT INTO @Table VALUES ('C')
INSERT INTO @Table VALUES ('D')
INSERT INTO @Table VALUES ('D')
INSERT INTO @Table VALUES ('E')


SELECT  MAX(RowNumber)
FROM    (SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Col), Col
         FROM   @Table) t
GROUP BY Col

Problem

I have an SQL Table which consists of 1 column only ``` Column Name A A A B B B B C D D E ``` I need an SQL Code that returns the cut points. For the table above, it will return this: ``` Column Name 3 7 8 10 11 ``` 3 is the end of A's and 7 is the end of B's and 8 is the end of C's and so on... Let's see what can you come up with :=)

Original source