Tricky SQL Problem

Question

I have a table like this ...

Key  Seq     Val 
A     1      123
A     4      129 
A     9      123
A     10     105 
B     3      100
B     6      101
B     12     102

I want to find cases (like A,4) where the value(in this case 123) is the same before (in this case A,1) and after (in this case A,9). The seq increases strictly, but may have gaps. Any suggestions?

Answer 1

Although I have only tested this in sql server 2005 (since I don't have a 2000 instance around), after replacing @t with a real table this should still work on that platform.

select k, seq, val
from (
    select k, seq, val,
          (select top 1 val from @t aux where aux.k = main.k and aux.seq < main.seq order by seq desc) as prev_val,
          (select top 1 val from @t aux where aux.k = main.k and aux.seq > main.seq order by seq asc) as next_val
    from @t main
) x
where prev_val = next_val

Provided you have an index on k, seq the performance shouldn't be too bad, as the correlated subqueries are simple index scans.

Sadly, I don't think support for the lag and lead functions is on the SQL Server roadmap.

[ In case anyone's interested, I mean that in some databases you can write:

select key, seq, val
from (
    select key, seq, val,
           lag(val) over(partition by key order by seq) as prev_val,
           lead(val) over(partition by key order by seq) as next_val
    from t
    ) x
where prev_val = next_val;

This would definitely come into its own if you wanted to look at the previous two or more values, because you can write lag(val, 2) to look 2 rows back etc. Finding the immediately previous or next value is a simpler case that select top 1 ... handles quite nicely. ]