distinct by a column 1

[pendarric]

Lets say we have a table with an id and a date
I want to find the most recent date for each id.
eg
62997 2000-12-16
63702 2000-02-02
100004 2001-03-14
62997 2000-02-22
100008 2000-03-26

should output
62997 2000-12-16
63702 2000-02-02
100004 2001-03-14
100008 2000-03-26


This is easy to do in a cursor but very slow. is there any way of doing it without a cursor or does anyone know of a fast cursor algorithm to do this

if anyone wants to bother i have included the code for how i do it. As you can see it is for reg dates of dogs. it take 12 mins with 24000 source rows when running on a nice pentium 3.

DECLARE @dogid int
DECLARE @feedate datetime
CREATE TABLE #MyTempTable (dogid INT, regdate datetime)
DECLARE Fee_Cursor CURSOR
LOCAL SCROLL FOR SELECT dogid,feedate FROM dogfeehistory ORDER BY feedate DESC
OPEN Fee_Cursor
FETCH NEXT FROM Fee_Cursor INTO @dogid,@feedate
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT(@dogid IN (SELECT dogid FROM #MyTempTable))
BEGIN
INSERT INTO #MyTempTable VALUES (@dogid,@feedate)
END
FETCH NEXT FROM Fee_Cursor INTO @dogid,@feedate
END
CLOSE Fee_Cursor
DEALLOCATE Fee_Cursor
SELECT * FROM #MyTempTable ORDER BY DogID
drop table #MyTempTable

 

[tlbroadbent]

It is very easy and fundamental to SQL. You use the aggregate functions, GROUP BY and MAX. You can read about them in SQL BOL.

Select ID, Max([Date]) As MaxDate
From tbl
Group By ID
Order By ID Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[pendarric]

ok call me stupid

but here is another one
lets say instead of getting the max we want to merge them

input
62997 'blue'
100004 'red'
62997 'green'
100008 'red'
100004 'black'

output
62997 'blue, green'
100004 'red, black'
100008 'red'