How to get Records from Date - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

How to get Records from Date

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-03-16 : 20:44:17

Hi

i have a table where i have duplicate record and i want to get one record based on from those duplicate which have most recent date.

MY DUPLICATE ARE BASED ON FIRSTNAME,LASTNAME,ADDRESS1,STATECODE I.E I WANT TO GROUP THOSE RESULT ON THE ABOVE FIELDS

CREATE TABLE [Table1] (
[OldCustomerCode] [varchar] (10)
[FirstName] [varchar] (50)
[LastName] [varchar] (50)
[Address1] [varchar] (255)
[StateCode] [varchar] (2) ,
[ZipCode] [varchar] (20) ,
[UpdatedOn] [datetime] NOT NULL
)

insert into table1('1','A','AD','154 KEEL','FL','12345','2002-06-18 23:14:18.000
')
insert into table1('2','A','AD','154 KEEL','FL','12345','2003-06-18 23:14:18.000
')
insert into table1('3','B','AAE','170 KSTEEL','CA','12945','2002-02-18 23:14:18.000
')
insert into table1('4','B','AAE','170 KSTEEL','CA','12945','2002-03-18 23:14:18.000
')
insert into table1('5','B','AAE','170 KSTEEL','CA','12945','2002-03-29 23:14:18.000
')
insert into table1('6','C','BBB','170 STEEL','CA','12900','2002-05-29 23:14:18.000
')
insert into table1('6','C','BBB','170 STEEL','CA','12900','2002-10-29 23:14:18.000
')

pLZ HELP ME

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-16 : 21:05:24

[code]select *
from table1 t
join
(
select FIRSTNAME,LASTNAME,ADDRESS1,STATECODE,max(updatedon) LASTUPDATEON
from table1
group by FIRSTNAME,LASTNAME,ADDRESS1,STATECODE
) d on d.FIRSTNAME = t.FIRSTNAME AND d.LASTNAME = t.LASTNAME
AND d.ADDRESS1 = t.ADDRESS1 AND d.STATECODE = t.STATECODE
AND d.LASTUPDATEON = t.UpdatedOn
ORDER BY OldCustomerCode[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources