Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

Catastrophic failure Unexpected method call sequence. 0x8000ffff (-2147418113)., ksh lock a file, [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed, sed: -e expression #1, char 1: unterminated address regex, procmail + change subject, w2ksp4.exe download, /proc/kallsyms format, sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm

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