I want to run a mysql query where the results are based on the value of the cell directly above the cell of interest. For example: I am calculating time spent in a series of behaviors and would like to identify instances where the duration of time spent walking is longer than the time spent feeding that occurred immediately prior to walking. Here is a sample data set:
ID Duration (min) Time of Day Behavior
1 21 9:01 Walk
1 31 9:22 Eat
1 15 9:53 Walk
1 21 10:14 Eat
2 7 1:00 Walk
2 9 1:07 Eat
2 4 1:16 Walk
I would like my query to identify the two rows in which the 'Duration' amount is smaller than for the previous row. IE: the third entry for ID1, and the third entry for ID2.
Any help would be greatly appreciated. Thx
Sample data:
CREATE TABLE t
(pkai int auto_increment primary key, `ID` int, `Duration` int, `TOD` time, `Behavior` varchar(4))
;
INSERT INTO t
(`ID`, `Duration`, `TOD`, `Behavior`)
VALUES
(1, 21, '9:01', 'Walk'),
(1, 31, '9:22', 'Eat'),
(1, 15, '9:53', 'Walk'),
(1, 21, '10:14', 'Eat'),
(2, 7, '01:00', 'Walk'),
(2, 9, '01:07', 'Eat'),
(2, 4, '01:16', 'Walk')
;
Note, that I added another column which is used as primary key (auto_increment). It makes things less complicated here and you should have a primary key anyway.
Query:
select ID, Duration, TOD, Behavior from (
select
t.*,
if(@prev_duration > Duration and @prev_id = ID, 1, 0) as prev_entry_longer,
@prev_duration := if(@prev_id != ID, null, Duration),
@prev_id := ID
from
t
, (select @prev_duration := Duration, @prev_id := ID from t order by pkai limit 1) var_init
where pkai != (select min(pkai) from t)
order by ID, TOD
) sq
where prev_entry_longer = 1;
Result:
| ID | DURATION | TOD | BEHAVIOR |
|----|----------|--------------------------------|----------|
| 1 | 15 | January, 01 1970 09:53:00+0000 | Walk |
| 2 | 4 | January, 01 1970 01:16:00+0000 | Walk |
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments