How to find consecutive unique values in a column ordered in the same way as the original table?

Question

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

What does this mean? "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." Lead() is the way to find unique next values, so I don't understand what it is exactly that you're "not sure how" to do. You might find it easier to use LAG(). — Tab Alleman, Aug 07 '19 at 18:21
I don't believe you are correct here. Lead() does not give you unique values, it just gives you the next value based on the order you specify in the over clause. — Komal Bachhuka, Aug 07 '19 at 18:34
Lead gives you the next value which lets you test whether it is the same as the previous value or different, which lets you decide if you want to include it in your results or not. You are mis-using the word "unique" here, by the way, if you really wanted unique values, you would only have a single row for each value...not 2 rows with value="A". That's not the definition of "unique" — Tab Alleman, Aug 07 '19 at 18:37
Duplicate: https://stackoverflow.com/questions/10110026/group-data-by-the-change-of-grouping-column-value-in-order — Tab Alleman, Aug 07 '19 at 18:58

score 1 · Accepted Answer · answered Aug 07 '19 at 18:23

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

score 0 · Answer 2 · answered Aug 07 '19 at 18:35

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, '')

score 0 · Answer 3 · answered Aug 07 '19 at 19:58

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)

How to find consecutive unique values in a column ordered in the same way as the original table?

3 Answers3