Post reply

I have a table #racetest from where I want to get two lines with max discharge dates with each year

  • May 3, 2011 at 2:00 pm

    #235634

    I want first two lines show up from the query

    iddischargedatefinalraceyear

    00001112010-12-04O2010

    00001112009-12-22O2009

    00001112009-03-13I2009

    #racetest table also has

    iddischargedatefinalraceyear

    00001112010-12-04O2010

    00001112009-12-22O2009

    00001112009-03-13I2009

    drop table #racetest1

    select distinct mrn,

    max(dischargedate)as dischargedate,

    finalrace,

    year

    into #racetest1 from #racetest

    group by mrn ,

    finalrace,

    year

    order by dischargedate desc, year

    Any help would be appreciated!,

    thanks,

    Haimanti

  • May 3, 2011 at 2:10 pm

    #1319383

    either

    SELECT TOP 2 ORDER BY dischargedate DESC

    or a CTE using

    ROW_NUMBER() OVER(PARTITION BY id ORDER BY dischargedate DESC) AS row

    and a query using WHERE row<=2


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 4, 2011 at 8:49 am

    #1319855

    Thanks a lot, it worked.

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

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