Selecting data row with the Min() value

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

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

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]

tallg
Starting Member

5 Posts

Posted - 2013-01-23 : 10:44:28

Excellent - Many thanks everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources