How to find consecutive unique values in a column

问题:

So I have a table with name ID, Time and Values. Values column has multiple same values. Time is of datetime2 datatype and is in ascending order of unique values. ID is also a primary key with int values. I want the result as continuous unique values of Values column in the same order as it appears in the original table.

I have tried using window function Lead to find next values from a given value of Values column, but I am not sure how to find unique next values.

Original table "TableTest"

ID   Time                             Value
1    2019-06-24 18:23:04.0400000       A
2    2019-06-24 18:23:04.0420000       A
3    2019-06-24 18:23:04.0450000       B
4    2019-06-24 18:23:04.0670000       A
5    2019-06-24 18:23:04.0690000       C
6    2019-06-24 18:23:04.0700000       C

Since the "A" with ID 4 is not coming in continuation with the "A" of ID 1, I want it in my result. Hence I want the result as below.

ID   Time                             Value
1    2019-06-24 18:23:04.0400000       A
3    2019-06-24 18:23:04.0450000       B
4    2019-06-24 18:23:04.0670000       A
5    2019-06-24 18:23:04.0690000       C

回答1:

Try this below-

WITH your_table(ID,Time,Value)
AS
(
SELECT 1,'2019-06-24 18:23:04.0400000','A' UNION ALL
SELECT 2,'2019-06-24 18:23:04.0420000','A' UNION ALL
SELECT 3,'2019-06-24 18:23:04.0450000','B' UNION ALL
SELECT 4,'2019-06-24 18:23:04.0670000','A' UNION ALL
SELECT 5,'2019-06-24 18:23:04.0690000','C' UNION ALL
SELECT 6,'2019-06-24 18:23:04.0700000','C'
)

SELECT A.ID,A.Time,A.Value
FROM
(
    SELECT *, LAG(Value) OVER(ORDER BY ID)  Lag_Value
    FROM your_table
)A
WHERE value <> Lag_Value OR Lag_Value IS NULL

回答2:

Actually you just need to filter out the rows with same [value] as the previous row. If your [id] column is not continious, then you need to use a ROW_NUMBER() function to generate continiou row id and join on it.

    WITH TABLE_TEST AS (
    SELECT 1 AS ID, CAST('2019-06-24 18:23:04.0400000' AS DATETIME2) AS Time, 'A' AS [VALUE] UNION
    SELECT 2 AS ID, CAST('2019-06-24 18:23:04.0420000' AS DATETIME2) AS Time, 'A' AS [VALUE] UNION
    SELECT 3 AS ID, CAST('2019-06-24 18:23:04.0450000' AS DATETIME2) AS Time, 'B' AS [VALUE] UNION
    SELECT 4 AS ID, CAST('2019-06-24 18:23:04.0670000' AS DATETIME2) AS Time, 'A' AS [VALUE] UNION
    SELECT 5 AS ID, CAST('2019-06-24 18:23:04.0690000' AS DATETIME2) AS Time, 'C' AS [VALUE] UNION
    SELECT 6 AS ID, CAST('2019-06-24 18:23:04.0700000' AS DATETIME2) AS Time, 'C' AS [VALUE] 
)
SELECT
        t1.ID
       ,t1.Time
       ,t1.value    
    FROM TABLE_TEST t1
    LEFT JOIN TABLE_TEST t2
        ON t1.id = t2.id + 1
    WHERE t1.value<> ISNULL(t2.value, '')

回答3:

Compare each row with its previous row and if value of Value is same ignore the current row. t1.ID = 1 because first row will always be in the output.

select  
    * 
from 
    TableTest t1, TableTest t2 
where 
    t1.ID = 1 or
    (t1.ID - 1 = t2.ID and
    t1.Value != t2.Value)