Sql Server Programming :: Need help for a Select-Statement


	Need help for a Select-Statement Hi, I have for example following table: ID Col1 Col2 --------------------------- 1 A Date1 2 A Date2 3 B Date3 4 B Date4 5 B Date5 i want select every Col1 with the latest Date (Col2): ID Col1 Col2 --------------------------- 1 A Date1 // Date1 > Date2 5 B Date5 // Date5 > Date3 and Date4 Can I do this in 1 statement? best regards Jan On 6 Jun, 10:14, "Jan Lorenz" <knarze@gmx.de> wrote: - Hide quoted text - > Hi, > I have for example following table: > ID Col1 Col2 > --------------------------- > 1 A Date1 > 2 A Date2 > 3 B Date3 > 4 B Date4 > 5 B Date5 > i want select every Col1 with the latest Date (Col2): > ID Col1 Col2 > --------------------------- > 1 A Date1 // Date1 > Date2 > 5 B Date5 // Date5 > Date3 and Date4 > Can I do this in 1 statement? > best regards > Jan If Id col is not so important then following query will work SELECT Col1, Max(COL2) FROM Table1 GROUPBY Col1 -----------------------------------------------Reply----------------------------------------------- On 6 Jun, 10:14, "Jan Lorenz" <knarze@gmx.de> wrote: - Hide quoted text - > Hi, > I have for example following table: > ID Col1 Col2 > --------------------------- > 1 A Date1 > 2 A Date2 > 3 B Date3 > 4 B Date4 > 5 B Date5 > i want select every Col1 with the latest Date (Col2): > ID Col1 Col2 > --------------------------- > 1 A Date1 // Date1 > Date2 > 5 B Date5 // Date5 > Date3 and Date4 > Can I do this in 1 statement? > best regards > Jan CREATE TABLE ##Test (ID INT IDENTITY, Col1 NVARCHAR(1), Col2 SMALLDATETIME) GO INSERT INTO ##Test (Col1, Col2) VALUES (N'A', '2006-10-10') INSERT INTO ##Test (Col1, Col2) VALUES (N'A', '2005-10-10') INSERT INTO ##Test (Col1, Col2) VALUES (N'B', '2001-10-10') INSERT INTO ##Test (Col1, Col2) VALUES (N'B', '2002-10-10') INSERT INTO ##Test (Col1, Col2) VALUES (N'B', '2003-10-10') GO SELECT ID, Col1, Col2 FROM ##Test JOIN (SELECT Col1 AS c1, MAX(Col2) AS c2 FROM ##Test GROUP BY Col1) AS maxtest ON ##Test.Col1 = maxtest.c1 AND ##Test.Col2 = maxtest.c2 GO DROP TABLE ##Test GO You will have duplicate records if the latest date value is not unique for any given Col1 value.