March 13, 2002 at 2:53 pm
Usage Table
UID, Date, Value
I want to return all UIDs and values that are less than today's date. That part is simple. However, there could be multiple records in the record set. I then need to select the max(date) of the records selected. I am just not sure how to write this in T-SQL. I basically need to do a max function per UID, but the max should return a value per UID that is less than today's date. Thoughts?
Sample Table
UID DATE VALUE
1 1/1 1
1 2/1 2
1 6/1 3
Today's date: 3/13/02
Query should return the following values:
1 2/1 2
March 13, 2002 at 5:46 pm
This should do the trick.
SELECT UID, [DATE], VALUE
FROM smpTbl
INNER JOIN
(
SELECT UID, MAX(DATE) AS MAXDATE
FROM smpTbl
WHERE [DATE] < '3/13/02'
GROUP BY UID
) AS smpBase
ON
smpTbl.UID = smpBase.UID AND
smpTbl.[DATE] = smpBase.MAXDATE
What happens is you subquery for each UID's MaxDate but it has to be less than the criteria. Then this derived table is join back to the original so we can output the value as well.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply