MySQL Forums
Forum List  »  General

find dissimilarity of values comparing two adjacent rows
Posted by: Khan Olid Mannan
Date: March 15, 2010 09:15AM

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.

Options: ReplyQuote




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.