Re: SQL - Selecting a value from the record before the current one (tough one)
OK, thanks everyone. Creating an alias for the table was the solution. I didn't know that was possible.
The following query will grace this app in the future:
SELECT empl, date, Time AS thistime, (SELECT MAX(Time) FROM Time_RawData WHERE Time < t.Time AND Date = t.Date AND t.Empl = Empl) AS prevtime
FROM Time_RawData AS t
ORDER BY date, empl, time;
edit:
Found out I also need a reference to the next record:
SELECT TRD_RunNr, empl, date, Time AS thistime, (SELECT MAX(Time) FROM Time_RawData WHERE Time < t.Time AND Date = t.Date AND t.Empl = Empl) AS prevtime, (SELECT TRD_RunNr FROM Time_RawData WHERE Time = (SELECT MIN(Time) FROM Time_RawData WHERE Time > t.Time AND Date = t.Date AND t.Empl = Empl) AND Date = t.Date AND t.Empl = Empl) AS nextrecord
FROM Time_RawData AS t
ORDER BY date, empl, time;
This subquery stuff rocks!
__________________
"Yay"
Last edited by Structural Integrity; 21 Apr 2005 at 07:47.
|