SQL - Selecting a value from the record before the current one (tough one)

[Structural Integrity]

Another SQL question from me, this one I doubt can even be done (though they say you can do everything with SQL no?).
I want to compose a record from which one field contains a value from the record inserted before. I need this to calculate a time-difference between two records.

The table looks as follows:
1 - 9:00
2 - 9:30
3 - 10:30

I want a result like:
1 - 9:00 - 0:00
2 - 9:30 - 9:00
3 - 10:30 - 9:30

I tried a few combinations and came as far as:
SELECT Time, (SELECT MAX(Time) FROM Time_RawData WHERE Time < Time AND Date = Time_RawData.Date) AS secs
FROM Time_RawData;

But no success so far.
I tried using aliases like:
SELECT Time as thistime, (SELECT MAX(Time) FROM Time_RawData WHERE Time < thistime AND Date = Time_RawData.Date) AS secs
FROM Time_RawData;
But that doesn't work either. I'm using Access as a test environment and it asks me for the value of "thistime" so it can not figure this backreference out.

So, how do I get the previous time in the current record?

[wu_trax]

can you add another field that refers to the row before? then you could join table with itself.
something like
select a.time, (b.time - a.time) as secs from time_raw_data a, time_raw_data b where a.last_id = b.id or something like that.
there probably is a better way, i just dont know enough about sql.
(i guess you probably want to avoid ids, so this is no help :/)

[Caesar2]

SELECT   a.Time AS End,
         iif((SELECT   TOP 1 Time
              FROM     Time_RawData
              WHERE    a.Time >Time_RawData.Time
              ORDER BY Time DESC) IS NULL,
              "0:00",
              (SELECT   TOP 1 Time
               FROM     Time_RawData
               WHERE    a.Time >Time_RawData.Time
               ORDER BY Time DESC)) AS Begin
FROM     Time_RawData AS a
ORDER BY a.Time;

Link the table in your FROM with the table in the query that's in your SELECT statement.
SELECT the highest value that is smaller than your End Time
REPLACE a NULL value with 0:00 (or something else).
Hope this what you wanted.

[Dante Hicks]

Usually when using subqueries it's easier to reference the table as well as the column name.

So for your bit of code :
SELECT
Time as thistime
(SELECT MAX("Time") FROM Time_RawData WHERE "Time" < Time2."ThisTime")
FROM Time_RawDate as Time2

That way the subquery knows which column you're referring to.

However, I have no idea whether this will work in Access. I think it would work in Sybase. A self-join should also work though as per wu_traxs suggestion.

Also, you really should refrain from calling columns things like "Date" or "Time", unless I'm misunderstanding what you're doing.

[queball]

You don't even need subqueries:
select a.t, ifnull(max(b.t),0) from data a left join data b on b.t<a.t group by a.t
This isn't a tough one.

[Structural Integrity]

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!