help appreciated with this select statement please

How can I do this in one query
I've got a table like this
ID RESOURCEID DATE QUANTITY
The Id is unique (but just a counter not of interest here), there may be one
resourceid for each date and an asscoiated quantity.
I want to select all resourceid (distinct) and I just want just the
resourceid and the date and quantity associated
with that post that is less than today but nearast in time.
To examplifY
if some records look like like this
ID RESOURCEID DATE QUANTITY
1 1 2003-12-01 1.3
2 1 2004-01-25 1.4
3 2 2002-01-01 1.7
4 2 2003-07-27 2.8
I want my select statement to return only the two following post
2 1 2004-01-25 1.4
4 2 2003-07-27 2.8
That is one post for each resource and the post should be the one were the
date is less than today but closest in time
I want to do this without using stored procedure or view, since I'm not
allowed to create objects in the database.SELECT id, resourceid, date, quantity
FROM SomeTable AS S
WHERE date =
(SELECT MAX(date)
FROM SomeTable
WHERE date < CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND resourceid = S.resourceid)
David Portas
SQL Server MVP
--