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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Selecting data row with the Min() value		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tallg
Starting Member

5 Posts
Posted - 01/23/2013 :  08:58:09  Show Profile  Reply with Quote
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
Edited by - tallg on 01/23/2013 08:59:15

jimf
Flowing Fount of Yak Knowledge

USA
2875 Posts
Posted - 01/23/2013 :  10:02:29  Show Profile  Reply with Quote
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 - 01/23/2013 :  10:22:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts
Posted - 01/23/2013 :  10:31:59  Show Profile  Reply with Quote 
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
Go to Top of Page

tallg
Starting Member

5 Posts
Posted - 01/23/2013 :  10:44:28  Show Profile  Reply with Quote
Excellent - Many thanks everyone.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000