Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Selecting data row with the Min() value

Author  Topic 

tallg
Starting Member

5 Posts

Posted - 2013-01-23 : 08:58:09
Hi All
Can someone help me how to select the row with min(E_DATE+E_Time) for each PATIID from the below table?


PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00001 AAA 01/01/2013 01/01/2013 14:00:00 A2505
00002 BBB 01/03/2012 03/04/2012 15:45:00 G2582
00002 BBB 01/01/2012 01/02/2012 13:45:00 P4484
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402
...
...


Results need to be like below (ie. min (E_DATE+E_TIME) for each PATID


PATID NAME A_DATE E_DATE E_TIME E_ID
00001 AAA 01/01/2013 01/01/2013 06:00:00 X9625
00002 BBB 01/01/2012 04/01/2012 11:45:00 M1402


Please note I'm using SQL2000.

Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-23 : 10:02:29
Since you're on SQL 2005, I'm assuming all dates and time are strings.

SET DATEFORMAT DMY

INSERT INTO @Table
SELECT '00001','AAA','01/01/2013','01/01/2013','06:00:00','X9625' UNION
SELECT '00001','AAA','01/01/2013','01/01/2013','14:00:00','A2505' UNION
SELECT '00002','BBB','01/03/2012','03/04/2012','15:45:00','G2582 ' UNION
SELECT '00002','BBB','01/01/2012','01/02/2012','13:45:00','P4484' UNION
SELECT '00002','BBB','01/01/2012','04/01/2012','11:45:00','M1402'


SELECT patid,NAME,A_DATE,E_DATE,E_TIME,E_ID
FROM
(
select patid,NAME,A_DATE,E_DATE,E_TIME,E_ID
,[rown] = ROW_NUMBER() over(partition by patid order by convert(datetime,E_DATE+' ' + E_TIME) asc)
from @Table
) t1

WHERE rown = 1

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

tallg
Starting Member

5 Posts

Posted - 2013-01-23 : 10:22:31
I'm using date times fields and your method works beautifully in 2005. Do you know how to do this in SQL2000?

Thanks
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-23 : 10:31:59
[code]Select T.* from Table T
inner join
(
Select PatId,MIN(convert(datetime,E_DATE+' ' + E_TIME))MINDATE
from Table
Group by PatId
)P on T.PatId = P.PatId and convert(datetime,T.E_DATE+' ' + T.E_TIME) = MINDATE[/code]
Go to Top of Page

tallg
Starting Member

5 Posts

Posted - 2013-01-23 : 10:44:28
Excellent - Many thanks everyone.
Go to Top of Page
   

- Advertisement -