Problem with Tricky SQL

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 (e.g. A, 4) where the value (in this case 123) remains the same (in this case A, 1) and after (in this case A, 9). Seq is strictly increasing, but may have spaces. Any suggestions?

Answer 1

Although I tested this only in sql server 2005 (since I don't have an instance of 2000), after replacing @t with a real table, it should still work on this 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 

If you have an index on k, seq , performance should not be too bad, since correlated subqueries are a simple index scan.

Unfortunately, I don’t think lag and lead support in the SQL Server roadmap.

[In case someone is 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 to your senses if you wanted to look at the previous two or more values, because you can write lag(val, 2) to look 2 lines back, etc. Finding directly the previous or next value is a simpler case where select top 1 ... does a great job. ]

Answer 2

I would not expect this to penetrate thousands of lines:

 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 join the table on themselves to find the two rows that surround the “interesting” row according to your definition. Subsequent joins (t4 and t5) ensure that the strings found by searching t2 and t3 are closest to t1.

Answer 3

Edit: I wrote this before you said SQL Server 2000. It works in SQL Server 2005 or later, so it will not help you, but I will leave it here for posterity :)

I use CTE to add sequential (continuous) order to the table, and then double-enter to get the 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 qk=q1.k join q q2 on q2.rownum=q.rownum+1 and qk=q2.k where q1.val=q2.val 

Answer 4

This works if you do not 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 

if you do this:

 ;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: tmptbl approach that does not give you 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 can be easily changed to give you seq.

Answer 5

Assuming the table name is "Table" is 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 )