Get the row with first occurrence of repetitive value in column using SQL Server

Question

Suppose I have this table

Id   Order Value
------------------
aa   0     'cat'
ba   1     'dog'
bb   2     'yuk'
dc   3     'gen'
ca   4     'cow'
c1   5     'owl'
b0   7     'ant'
h9   8     'fly'
t4   9     'bee'
g2   10    'fox'
ea   11    'rat'
fa   12    'pig'
gu   13    'pig'
co   14    'pig'
fo   15    'pig'
ou   16    'pig'
eo   17    'pig'
ii   18    'pig'

What is the query in order to obtain:

fa   12    'pig'

Like you can see, the next rows after 12 till 18. All these rows have a pig in the Value column.

How determine the row where the repetition began?

Thanks

As in: the first row for which the next row has the same `Value` when ordered by `Order`? What have you tried so far? — Charlieface, May 02 '21 at 03:59

score 1 · Accepted Answer · answered May 02 '21 at 04:28

You can use the WITH TIES option in concert with the window functions lead() and row_number()

Example

Declare @YourTable Table ([Id] varchar(50),[Order] int,[Value] varchar(50))  Insert Into @YourTable Values 
 ('aa',0,'cat')
,('ba',1,'dog')
,('bb',2,'yuk')
,('dc',3,'gen')
,('ca',4,'cow')
,('c1',5,'owl')
,('b0',7,'ant')
,('h9',8,'fly')
,('t4',9,'bee')
,('g2',10,'fox')
,('ea',11,'rat')
,('fa',12,'pig')
,('gu',13,'pig')
,('co',14,'pig')
,('fo',15,'pig')
,('ou',16,'pig')
,('eo',17,'pig')
,('ii',18,'pig')
 
Select top 1 with ties *
 From @YourTable
 Order By case when lead(value,1) over (order by [order]) = value then 1 else 2 end
         ,row_number() over (order by [Order])

Results

Id  Order   Value
fa  12      pig

score 0 · Answer 2 · answered May 02 '21 at 04:39

If you want the first row where a repetition occurs, you can use LEAD to get the next value per row, ORDER BY to sort and TOP (1) to get the first result

SELECT TOP (1) *
FROM (
    SELECT *,
        LEAD([Value]) OVER (ORDER BY [Order]) nextValue
    FROM tbl
) t
WHERE nextValue = [Value]
ORDER BY [Order];

If you want the first row for every repetition, you can use LEAD to get the next value per row, and LAG to get the one before. Then you check whether the next value is the same, but the previous one is different

SELECT *
FROM (
    SELECT *,
        LEAD([Value]) OVER (ORDER BY [Order]) nextValue,
        LAG([Value]) OVER (ORDER BY [Order]) prevValue
    FROM tbl
) t
WHERE nextValue = [Value]
AND (prevValue <> [Value] OR prevValue IS NULL)
ORDER BY [Order];

Get the row with first occurrence of repetitive value in column using SQL Server

2 Answers2