TOP 1 Per Group

  • How can I get the TOP 1 result per group, not per result set?  Example:

    --DROP TABLE #Test

    CREATE TABLE #Test (

      ID int IDENTITY (1,1),

      FKID int,

      Item varchar(20),

      Atime smalldatetime)

    INSERT INTO #Test

    SELECT 202, 'Paper', '2007-08-14'

    UNION

    SELECT 202, 'Rock', '2007-08-14'

    UNION

    SELECT 202, 'Scissors', '2007-08-13'

    UNION

    SELECT 800, 'Paper', '2007-08-16'

    UNION

    SELECT 802, 'Candy', '2007-08-15'

    UNION

    SELECT 802, 'Apple', '2007-08-15'

    Looking for TOP 1 Item

    with MAX(Atime)

    grouped by FKID

    All I really want is the Item with the latest date for each FKID.  The problem I have is when the dates are the same I get more than one row returned.  Don't care which item gets returned.  Just want one item, with the latest date for each FKID.

  • SELECT t.FKID, MAX(t.Item) Item, t.ATime

    FROM #Test t INNER JOIN (

    select FKID,  MAX(ATime) ATime FROM #Test

    GROUP BY FKID ) t1 ON t.FKID = t1.FKID AND t.[ATime] = t1.[ATime]

    GROUP BY t.[FKID], t.[ATime]

  • David,

    You can use the following query :

    selecta.fkid,

    max(a.item)item,

    max(a.atime)Atime

    from#test a

    inner join (select

    fkid,

    max(atime) Atime

    from #test

    group by fkid

    )b ona.fkid = b.fkid and

    a.atime = b.atime

    group by a.fkid

  • DY-NO-MITE!!!!!!!!!!!

    Thanks

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

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