August 24, 2007 at 11:38 am
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.
August 24, 2007 at 12:15 pm
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]
August 24, 2007 at 12:17 pm
David,
You can use the following query :
select a.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 on a.fkid = b.fkid and
a.atime = b.atime
group by a.fkid
August 24, 2007 at 12:20 pm
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