query for unique record based on maxdatetime

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)

query for unique record based on maxdatetime

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-05-11 : 13:19:37

I have a table which looks like

VisitID DateTime Response
FN10000061559 1/19/07 4:49 PM 1
FN10000166954 8/17/06 2:14 PM 1
FN10000198818 8/30/06 1:04 PM 1
FN10000201615 9/29/06 5:45 PM 1
FN10000208586 11/1/06 12:24 PM Trf
FN10000216866 8/23/06 12:19 PM Colmb
FN10000217653 12/22/06 10:00 AM FIELD HOME
FN10000227122 8/24/06 10:39 AM HAB
FN10000235855 8/11/06 11:00 AM lhc
FN10000235855 8/11/06 11:29 AM wph

i would like to have the output as only one row for each visitID with Response for the Max(DateTime). for example there are two rows for VisitID FN10000235855 with different DateTimes and different responses. i would like to have only one row with max date time in this case 8/11/06 11:29 Am with response field being wph.

please suggest.

Thanks,

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-11 : 13:38:41

SELECT VisitID, DateTime, Response
FROM YourTable AS T1
WHERE NOT EXISTS(SELECT *
FROM YourTable AS T2
WHERE T2.VisitID = T1.VisitID
AND T2.DateTime > T1.DateTime)

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-11 : 13:40:24

Try this ...

declare @temp table(a varchar(40), b datetime, c varchar(20))

insert into @temp values('FN10000061559', ' 1/19/07 4:49 PM', '1')
insert into @temp values('FN10000061559', ' 8/17/06 2:14 PM', ' 1')
insert into @temp values('FN10000061559', ' 8/30/06 1:04 PM ', '1')
insert into @temp values('FN10000201615', ' 9/29/06 5:45 PM ', '1')
insert into @temp values('FN10000208586', ' 11/1/06 12:24 PM', ' Trf')
insert into @temp values('FN10000216866', ' 8/23/06 12:19 PM', ' Colmb')
insert into @temp values('FN10000216866', ' 12/22/06 10:00 AM', ' FIELD HOME')
insert into @temp values('FN10000227122', ' 8/24/06 10:39 AM', ' HAB')
insert into @temp values('FN10000235855', ' 8/11/06 11:20 AM', ' lhc')
insert into @temp values('FN10000235855', ' 8/11/06 11:29 AM', ' wph')

-- original ----
select * from @temp

-- result ---
select a, b, c from (select *, rid = row_number() over(partition by a order by b desc) from @temp) t
where rid = 1 order by a

-- original ----
a b c
---------------------------------------- ----------------------- --------------------
FN10000061559 2007-01-19 16:49:00.000 1
FN10000061559 2006-08-17 14:14:00.000 1
FN10000061559 2006-08-30 13:04:00.000 1
FN10000201615 2006-09-29 17:45:00.000 1
FN10000208586 2006-11-01 12:24:00.000 Trf
FN10000216866 2006-08-23 12:19:00.000 Colmb
FN10000216866 2006-12-22 10:00:00.000 FIELD HOME
FN10000227122 2006-08-24 10:39:00.000 HAB
FN10000235855 2006-08-11 11:20:00.000 lhc
FN10000235855 2006-08-11 11:29:00.000 wph

(10 row(s) affected)

-- result ---
a b c
---------------------------------------- ----------------------- --------------------
FN10000061559 2007-01-19 16:49:00.000 1
FN10000201615 2006-09-29 17:45:00.000 1
FN10000208586 2006-11-01 12:24:00.000 Trf
FN10000216866 2006-12-22 10:00:00.000 FIELD HOME
FN10000227122 2006-08-24 10:39:00.000 HAB
FN10000235855 2006-08-11 11:29:00.000 wph

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-11 : 15:29:47

[code]SELECT T.*
FROM table_name T
CROSS APPLY (SELECT 1
FROM table_name
WHERE visitID = T.visitID
HAVING MAX(DateTime) = T.DateTime)D(i)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources