help with sql-server query - dates

Question

Hi

I have this table:

id|date
----
1 | 10/11/2009
2 | 13/11/2009
1 | 20/12/2009
3 | 21/12/2009
1 | 30/12/2009

If I stand on the last record (id=1) and I need to see the last date where id=1 is appear --> will show me: 1 | 20/12/2009

What query will do it ?

Thanks in advance

Answer 1

I'm assuming that what you want is to find the next, preceding record matching the id in time sequence and that there may be additional records matching the id in sequence after the record from which you want to do this calculation. If the records are ordered in time sequence and you know the value of the date for the current record of interest you can do:

select max(date) from table
where date < @currentDate and id = @currentId

where @currentDate and @currentId are set to the date and id of the row in question.

If you need to pull the exact, entire row where this date occurs, then you'll need to first select the date in a subquery, then find the row(s) that match the (local) max date and id.

select * from table
where id = @currentId
      and date = (select max(date) from table
                  where date < @currentDate and id = @currentId)