Luis Cazares (8/15/2012)
DECLARE @Tabletable(
idint,
device int,
value int)
INSERT @Table
SELECT 9, 456, 70 UNION ALL
SELECT 8, 456, 60 UNION ALL
SELECT 7, 123, 70 UNION ALL
SELECT 6, 123, 60 UNION ALL
SELECT 5, 456, 50 UNION ALL
SELECT 4, 456, 10 UNION ALL
SELECT 3, 123, 50 UNION ALL
SELECT 2, 123, 20 UNION ALL
SELECT 1, 123, 10 ;
WITH CTE AS (
SELECT id,
device,
value,
ROW_NUMBER() OVER(ORDER BY 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
This will only work if the ID for each DEVICE is always next to the previous DEVICE.
Try this sample data instead: -
DECLARE @Table TABLE (id INT, device INT, value INT);
INSERT @Table
SELECT 5, 456, 50
UNION ALL SELECT 4, 456, 10
UNION ALL SELECT 3, 123, 50
UNION ALL SELECT 2, 456, 20
UNION ALL SELECT 1, 123, 45;
Expected result would be ID 5, because it is the only one where the previous ID for the device is more than 20. Your code would instead report back 3 and 5.
If the OP does only want the previous row, then you'd be better off using the ID column instead of a ROW_NUMBER.
Adi Cohn-120898 (8/15/2012)
ideclare @tbl table (ID int, DEVICE int, VALUE int)
insert into @tbl (ID, DEVICE, VALUE) VALUES (9, 456, 70)
insert into @tbl (ID, DEVICE, VALUE) VALUES (8, 456, 60)
insert into @tbl (ID, DEVICE, VALUE) VALUES (7, 123, 70)
insert into @tbl (ID, DEVICE, VALUE) VALUES (6, 123, 60)
insert into @tbl (ID, DEVICE, VALUE) VALUES (5, 456, 50)
insert into @tbl (ID, DEVICE, VALUE) VALUES (4, 456, 10)
insert into @tbl (ID, DEVICE, VALUE) VALUES (3, 123, 50)
insert into @tbl (ID, DEVICE, VALUE) VALUES (2, 123, 20)
insert into @tbl (ID, DEVICE, VALUE) VALUES (1, 123, 10)
select t2.ID
from @tbl as t1 inner join @tbl as t2 on t1.ID = t2.ID -1
where t2.VALUE - t1.VALUE > 20
Same reasons as above for why this wouldn't work, unless the OP wants to ignore the device.