SQL Query Question

[matt cole]

Hi, I have a question about a sql query I need to do. I have a table with 3 fields, userID, entryDate and value. My query will have a date parameter called targetDate. What I was hoping to do was retrieve a single row for each user where the entryDate is the most recent entry for that user prior to the targetDate parameter. If there are no entries prior the the targetDate for the user, no rows will be returned for that user, if they have multiple entries with entryDate < targetDate, obviously only the most recent will be returned. If anyone was able to help me with the SQL for this, that'd be great. Thanks, Matt

[Jon Hulatt]

You have do do this with a two pronged approach. Use can use max() and group by clause to get the newest date (only) for each customer, then you can join back on the table to get the rest of the data:-

select yourTable.* from (
select userID, max(entryDate) as entryDate
from yourTable
group by userid
) k
inner join yourTable
on yourTable.userId = k.userId and yourTable.entryDate = k.entryDate

using System.Beer;

[Anonymous]

Thanks Jon! That works perfectly :)

[matt cole]

Thanks Jon, That works perfectly :)