Select only unique, sequential rows in TSQL

user_1411058 0
Viewed 66

There have been a couple of previous questions (1 & 2) that are quite close to what I'm trying to do, but haven't been able to tease the answer out.

In my case, I have data that may contain multiple sequential duplicates, so starting from where Martin did:

e.g. 

id      companyName
-------------------    
1       dogs ltd
2       cats ltd
3       pigs ltd
4       pigs ltd
5       pigs ltd
6       cats ltd
7       cats ltd
8       dogs ltd
9       pigs ltd

I want to return

companyName
-----------    
dogs ltd
cats ltd
pigs ltd
cats ltd
dogs ltd
pigs ltd

eliminating the sequential duplicates, but keeping the unique values in order. In all the previous questions, using the lead and lag functions, that would only look one ahead or behind, not allowing for 3 or more duplicated sequential values.

tsql sql-server
1 Answers

This will do it:

SELECT companyName
FROM
    (
        SELECT
            id
            , companyName
            , LEAD(companyName, 1, NULL) OVER (ORDER BY id) lead
        FROM yourTable
    ) Q
WHERE
    companyName <> lead
    OR lead IS NULL
ORDER BY id
user_53145300
Related