Aggregate function in comparison of 2 rows in the same table (SQL)

Question

Given the table definition:

create table mytable (
    id integer,
    mydate datetime,
    myvalue integer )

I want to get the following answer by a single SQL query:

id date_actual value_actual date_previous value_previous

where:

date_previous is the maximum of all the dates preceeding date_actual 
for each id and values correspond with the two dates 
{max(date_previous) < date_actual ?}

How can I achieve that?

Thanks for your hints

Answer 1

This is a variation of the common "greatest N per group" query which comes up every week on StackOverflow.

SELECT m1.id, m1.mydate AS date_actual, m1.myvalue AS value_actual,
  m2.mydate AS date_previous, m2.myvalue AS value_previous
FROM mytable m1 
LEFT OUTER JOIN mytable m2 
  ON (m1.id = m2.id AND m1.mydate > m2.mydate)
LEFT OUTER JOIN mytable m3 
  ON (m1.id = m3.id AND m1.mydate > m3.mydate AND m3.mydate > m2.mydate)
WHERE m3.id IS NULL;

In other words, m2 is all rows with the same id and a lesser mydate, but we want only the one such that there is no row m3 with a date between m1 and m2. Assuming the dates are unique, there will only be one row in m2 where this is true.

Answer 2

Assuming I understood your requirements correctly, here's something you can try.

select a.id, 
       a.mydate as date_actual, 
       a.value as value_actual,
       b.date as date_previous, 
       b.value as value_previous
from mytable a, mytable b
where a.id = b.id and
      a.mydate > b.mydate and
      b.mydate = (select max(mydate) from mytable c where c.id = a.id and c.mydate < a.mydate)

Apologies for the ugly SQL. I am sure there are better ways of doing this.

Answer 3

Bill and Rahul,

thank yuo for your help and quick response, it really helped me a lot!