|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
 Posted - 01/23/2013 : 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 |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 01/23/2013 : 10:31:59
|
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 |
|
|
Selecting data row with the Min() value
