find dissimilarity of values comparing two adjacent rows
Hi,
I have a table having 50000 rows, i just want to compare "rec_val" column of each row with 'previuos' and 'next' row of same unique id "rec_id". example:
rec_id - rec_val - rec_date
1001 - 20.00 - 2010-01-01
1001 - 17.00 - 2010-01-02
1001 - 897.00 - 2010-01-03
1001 - 10.00 - 2010-01-04
1001 - 21.00 - 2010-01-05
1001 - 14.00 - 2010-01-06
1001 - 05.00 - 2010-01-07
1001 - 11.00 - 2010-01-08
1001 - 12.00 - 2010-01-09
1001 - 19.00 - 2010-01-10
***LOOK! the 3rd row suddenly giving a high value, And i just need to DETECT those rows that got a value which are suddenly 200% to 800% different than it's two adjacent rows.
I tried adding ROWNUM with my sql command, but still not successful with the tricks:
SELECT @row := @row +1 AS rownum, k.rec_id, k.rec_val, k.rec_date
FROM (SELECT @row :=0) r,
(
SELECT rec_id, rec_val, rec_date FROM tbl_records
where rec_date between '2009-11-01' and '2009-12-31'
and rec_id=1001
order by rec_date
) as k;
CAN ANYONE PLEASE HELP?
Regards. /KM.
Edited 1 time(s). Last edit at 03/15/2010 09:44AM by Khan Olid Mannan.
Subject
Written By
Posted
find dissimilarity of values comparing two adjacent rows
March 15, 2010 09:15AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.