Post reply

Get the top record

  • May 18, 2011 at 3:58 pm

    #238392

    Hi I have values like below:

    TermianlID SchedLoadDate Amount

    000000ABC1 2011-03-24 00:00:00.000 24000.00

    000000ABC1 2011-03-17 00:00:00.000 24000.00

    000000ABC1 2011-03-10 00:00:00.000 24000.00

    000000ABC1 2011-03-03 00:00:00.000 24000.00

    2B76 2011-05-17 00:00:00.000 0.00

    2B76 2011-02-22 00:00:00.000 42000.00

    2B76 2011-01-25 00:00:00.000 46000.00

    I want to get the result set like below

    TermianlID SchedLoadDate Amount

    000000ABC1 2011-03-24 00:00:00.000 24000.00

    2B76 2011-05-17 00:00:00.000 0.00

    Below is my query:

    SELECT stag.TERMINAL_ID,MAX(CL.SchedLoadDate) AS SchedLoadDate, CL.Amount FROM dbo.A CL

    INNER JOIN dbo.B stag

    ON terminalid = stag.TERMINAL_ID

    WHERE CL.SchedLoadDate < stag.REPORT_DATEGROUP BY stag.TERMINAL_ID,CL.SchedLoadDate,CL.Amount

    order by stag.TERMINAL_ID,CL.SchedLoadDate DESC

    Please help.

    Thanks,

  • May 18, 2011 at 4:08 pm

    #1326819

    SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY TermianlID ORDER BY SchedLoadDate, Amount DESC /*In case you need a tie breaker for 1st*/) AS Group_RowID FROM dbo.tbl) dta

    WHERE Groupe_RowID = 1

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply