scott_lotus (8/15/2012)
Edit to say, not sure is UNION is a good idea, find it very slow on + billions records.
The UNIONs that some have posted are part of creating sample data so that they could test their solutions (really this is something you should have provided with your post).
The actual solutions on offer are as follows: -
Cadavre
SELECT a.ID, a.DEVICE, a.VALUE
FROM (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) a
LEFT OUTER JOIN (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) b ON a.DEVICE = b.DEVICE AND a.pos = b.pos+1
WHERE a.VALUE-b.VALUE > 20;
Luis Cazares
WITH CTE AS (
SELECT id,
device,
value,
ROW_NUMBER() OVER(ORDER BY device, id) rownum
FROM @Table)
SELECT a.id
FROM CTE a
JOIN CTE b ON a.rownum = b.rownum + 1
WHERE a.value - b.value > 20;
Adi Cohn-120898
select t2.ID
from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1
where t2.VALUE - t1.VALUE > 20
Luis Cazares (8/15/2012)
I agree with you Cadavre, but I realized that after posting and realizing the OP was saying devices instead of ids. I corrected it before I saw your new post.
Yep, that fixes the issue with your code. Amusingly it now produces the exact same execution plan as mine 😉