Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

#1: first minimum date in the column for each distinct column a value

Posted on 2008-04-21 20:21:56 by techsatish

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

Report this message

#2: Re: first minimum date in the column for each distinct column a value

Posted on 2008-04-21 20:46:58 by Plamen Ratchev

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

Report this message