MySQL Forums :: General :: find dissimilarity of values comparing two adjacent rows


Advanced Search

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


Subject Written By Posted
find dissimilarity of values comparing two adjacent rows Khan Olid Mannan 03/15/2010 09:15AM
Re: find dissimilarity of values comparing two adjacent rows Rick James 03/19/2010 08:36AM
Re: find dissimilarity of values comparing two adjacent rows Khan Olid Mannan 03/19/2010 04:35PM
Re: find dissimilarity of values comparing two adjacent rows Rick James 03/19/2010 08:09PM
Re: find dissimilarity of values comparing two adjacent rows Khan Olid Mannan 03/22/2010 04:00AM
Re: find dissimilarity of values comparing two adjacent rows Rick James 03/22/2010 09:57AM


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.