Value of column in row with previous key

Question

I'm writing an application which tracks views of various items across time, and I've hit a bit of a snag. I need to get the difference in views between two adjacent updates. The updates are identified by a UNIQUE key, containing the columns time and id. How would I be able to do this, without running multiple queries?

SELECT updates.views, (updates.views - <previous views>) 
FROM updates JOIN stuff ON stuff.id = stuff.id 
WHERE updates.time = '01-01-1970 00:00:00' AND stuff.owner = 'someone'

Answer 1

You could select the previous maximum in a subquery:

SELECT     updates.views
,          (updates.views - (
               select max(prevupd.views) 
               from updates prevupd 
               where prevupd.id <> updates.id
           ))
FROM updates 
JOIN stuff ON stuff.id = stuff.id 
WHERE updates.time = '01-01-1970 00:00:00' 
AND stuff.owner = 'someone'

Some example data would be nice, as Larry Lustig suggests.

Answer 2

In Oracle and PostgreSQL 8.4:

SELECT  views - LAG(views) OVER (ORDER BY time, id)
FROM    updates
JOIN    stuff
ON      stuff.id = updates.stuffid 
WHERE   updates.time = '01-01-1970 00:00:00'
        AND stuff.owner = 'someone'

In MySQL:

SELECT  views - @pviews AS diff,
        @pviews := views
FROM    (
        SELECT  @pviews := NULL
        ) vars
CROSS JOIN
        updates
JOIN    stuff
ON      stuff.id = updates.stuffid 
WHERE   updates.time = '01-01-1970 00:00:00'
        AND stuff.owner = 'someone'
ORDER BY
        time, id

Answer 3

This should work in pretty much any RDBMS:

SELECT
     UPD.id,
     UPD.views,
     (UPD.views - COALESCE(LAST_UPD.views, 0))
FROM
     Updates UPD
INNER JOIN Stuff S ON
     S.id = UPD.id AND
     S.owner = 'someone'
INNER JOIN Updates LAST_UPD ON
     LAST_UPD.id = UPD.id AND
     LAST_UPD.time < UPD.time
LEFT OUTER JOIN Updates UPD2 ON
     UPD2.id = LAST_UPD.id AND
     UPD2.time < UPD.time AND
     UPD2.time > LAST_UPD.time
WHERE
     UPD2.id IS NULL AND
     UPD.time = '01-01-1970 00:00:00'

What you're basically doing is saying, "Give me a previous update (LAST_UPD.time < UPD.time) for which there are no other updates after it and before this one (the left join and UPD2.id IS NULL).