On Tue, 21 May 2002, David Shields wrote: > Probably a very simple one for you guys, but I'm stuck. > Given a simple table (events): > id int > st time not null > et time not null > > I know I can get duration by converting st and et to secs and subtracting > (that bits easy), but how would I go about getting the gap between > successive records IN MY SQL STATEMENT (without processing the result set > in my application language (php)): i.e. > id st et > 1 09:00 10:00 > 2 10:15 10:45 > 3 11:45 12:00 > ... > i.e. my result wants to be (assuming data is good, and sort by st) > > id st et gap_since_last_row > 1 09:00 10:00 0 (no prev row) > 2 10:15 10:45 0:15 > 3 11:45 12:00 1:00 > ... > I suspect I should be using SQL variables, but docs seem a bit opaque. > > Anyone point me in right direction ?
David, get the manual, look at the JOIN command. You have to LEFT JOIN the table to itself on the condition that t2.id = (t1.id - 1) that looks like: SELECT ANY_FUNCTION(t1.st, t2.et) FROM mytable AS t1 LEFT JOIN mytable AS t2 ON t2.id = (t1.id -1) WHERE ... This gives you access to the columns of two subsequent rows and you may process the data as you like. I did not test this, but it should work. For the first row, as expected, all columns of the right table t2 are set to NULL. Thomas --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
