Tricky SQL Problem

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?

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. ]

I wouldn't expect this to blaze through thousands of rows:

SELECT
    * /* TODO - pick columns */
FROM
    Table t1
        inner join
    Table t2
        on
            t1.Key = t2.Key and
            t1.Seq < t2.Seq
        inner join
    Table t3
        on
            t1.Key = t3.Key and
            t1.Seq > t3.Seq and
            t2.Val = t3.Val
        left join
    Table t4
        on
            t1.Key = t4.Key and
            t1.Seq < t4.Seq and
            t4.Seq < t2.Seq
        left join
    Table t5
        on
            t1.Key = t5.Key and
            t1.Seq > t5.Seq and
            t5.Seq > t3.Seq
WHERE
     t4.Key is null and t5.Key is null

Basically, the first 3 instances of the table joins the table on itself to find two rows which surround an "interesting" row, per your definition. The subsequent joins (t4 and t5) ensure that the rows found by the t2 and t3 searches are the closest to the t1 row.

Edit: I wrote this before you said SQL Server 2000. This works in SQL Server 2005 or later, so it won't help you, but I'll leave it here for posterity :)

I'm using a CTE to add a sequential (unbroken) ordering to the table, then joining twice to get previous and next rows.

declare @t table (k char(1), seq int, val int)

insert into @t values ('A', 1, 100)
insert into @t values ('A', 4, 101)
insert into @t values ('A', 9, 100)
insert into @t values ('A', 10, 105)
insert into @t values ('B', 3, 100)
insert into @t values ('B', 6, 101)
insert into @t values ('B', 12, 102)

; with q as (
    select *, row_number() over (partition by k order by seq) [rownum] from @t
)     
select * 
from q
join q q1 on q1.rownum=q.rownum-1 and q.k=q1.k
join q q2 on q2.rownum=q.rownum+1 and q.k=q2.k
where q1.val=q2.val

This works if you don't need the seq field:

;with cte as
(
select COUNT( 1 ) as cnt, val, [key] from tbl 
group by val, [key]
)
select * from cte where cnt > 1

this if you do:

;with cte as
(
select COUNT( 1 ) as cnt, val, [key] from tbl 
group by val, [key]
)
select tbl.* from tbl inner join cte on cte.cnt > 1 and cte.[Key] = tbl.[Key] and cte.Val = tbl.Val

EDIT: A tmptbl approach that doesn't give you the seq:

CREATE TABLE #tmptbl (
    cnt int,
    [key] nchar(10),
    Val nchar(10)
 )

insert into #tmptbl
select COUNT( 1 ) as cnt, [key], Val from tbl 
group by tbl.Val, tbl.[key]
select * from #tmptbl where cnt > 1

drop table #tmptbl

Depending on the types of your fields this may be easy to change to give you the seq as well.

Assuming the name of the table is "Table" here is the plain vanilla sql.

SELECT 
Key, 
Seq 
from Table A
WHERE EXISTS
 (SELECT 1 FROM Table B, Table C 
   WHERE B.Key = A.Key
     AND C.Key = A.Key
     AND B.Seq = (SELECT MAX(Seq) FROM Table D WHERE D.Key = A.Key AND D.Seq < A.Seq) --This ensures that B retrieves previous row
     AND C.Seq = (SELECT MIN(Seq) FROM Table E WHERE E.Key = A.Key AND E.Seq > A.Seq) --This ensures that C retrieves next row
     AND B.Val = C.Val
 )