Select MAX date that is less than a specfied date

[jgombala]

I am trying to select the MAX date and the revenue amount that corresponds with that date that is less than a date specified.

nID nRevAmt
----------- ----------- ---------------------------
45 0 2001-02-10 00:00:00
45 3628 1999-10-05 00:00:00
45 3810 1999-10-28 00:00:00
45 4335 1999-11-16 00:00:00
45 4603 2000-12-01 00:00:00
45 4755 1999-11-24 00:00:00

If I enter a date of 2000-12-25 I want the following record returned:

nID nRevAmt
----------- ----------- ---------------------------
45 4603 2000-12-01 00:00:00

Thanks
Jeff

 

[tlbroadbent]

Select a.nID, a.nRevAmt, a.nDate
From tbl a
Inner Join
(Select nID, Max(nDate) As MaxDate
From tbl Where nDate<'2000-12-25'
Group By nID) As b
On a.nID=b.nID
And a.nDate=b.MaxDate
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views.&quot; - Abraham Lincoln

 

[carp]

SELECT nID, nRevAmt, nDate FROM my_table
WHERE nDate=(SELECT MAX(nDate) FROM my_table WHERE n_Date<specified_date);