Post reply

Tricky SQL

  • March 13, 2002 at 2:53 pm

    #78411

    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

    #425339

    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