Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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:

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:

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.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc