Prev: The Sql ranking OVERture
Next: How to find records with length greater than 17
From: rajesh on 21 Apr 2008 14:21
Hi ,

i want to select the first minimum date in the column d for each
distinct column a value.
how i can do that in a sql query.

xyztable

Column a Column b Column c Column d

6014350 -10 2008-02-22 00:00:00 2008-02-12 00:00:00
6014350 4 2008-02-22 00:00:00 2008-02-26 00:00:00
6014472 -7 2008-03-06 00:00:00 2008-02-28 00:00:00
6014472 32 2008-03-06 00:00:00 2008-04-07 00:00:00

i need the results like this....

Column a Column b Column c Column d
6014350 -10 2008-02-22 00:00:00 2008-02-12 00:00:00
6014472 -7 2008-03-06 00:00:00 2008-02-28 00:00:00

Thanks
Rajesh kumar
From: Plamen Ratchev on 21 Apr 2008 14:46
Two ways:

-- SQL Server 2000
SELECT cola, colb, colc, cold
FROM Xyz AS A
WHERE A.cold = (SELECT MIN(B.cold)
FROM Xyz AS B
WHERE B.cola = A.cola)


-- SQL Server 2005
WITH RankedXyz
AS
(SELECT cola, colb, colc, cold,
ROW_NUMBER() OVER(
PARTITION BY cola
ORDER BY cold) AS seq
FROM Xyz)
SELECT cola, colb, colc, cold
FROM RankedXyz
WHERE seq = 1;

HTH,

Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: The Sql ranking OVERture
Next: How to find records with length greater than 17