How to remove except recent date record (in duplic

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)

How to remove except recent date record (in duplic

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-06 : 19:06:55

Hello I have some data as in below table, how can i delete duplicate records (base on EMPNO only) except the most vacationDate record

for example with below data, for empno101 the record except 05/07/2008 (last record) all other records for 101 needs to be delete, like so for 101 02/12/2008 needs to be leave remain all needs to delete (only leave recent date employees (if they are duplicated))

Empno,Name,VacationDate,tourCountry
101,Anand,01/27/2008,SA
102,Kiran,01/25/2008,IND
101,Anand,02/12/2008,USA
102,Kiran D,03/17/2008,MEX
104,Babu,06/2/2008,AUS
105,Venk,04/7/2008,UK
106,Dan,03/04/2008,FRA
101,Anand Kumar,05/07/2008,UAE

Please help me Sir,

Thanks in advance

Best Regards
asita

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-06 : 19:24:27

Probably the simplest way is with the ROW_NUMBER() function:

DECLARE @Table TABLE (Empno INT,Name VARCHAR(20),VacationDate DATETIME, tourCountry VARCHAR(3))

INSERT @Table
SELECT 101,'Anand','01/27/2008','SA'
UNION ALL SELECT 102,'Kiran','01/25/2008','IND'
UNION ALL SELECT 101,'Anand','02/12/2008','USA'
UNION ALL SELECT 102,'Kiran D','03/17/2008','MEX'
UNION ALL SELECT 104,'Babu','06/2/2008','AUS'
UNION ALL SELECT 105,'Venk','04/7/2008','UK'
UNION ALL SELECT 106,'Dan','03/04/2008','FRA'
UNION ALL SELECT 101,'Anand Kumar','05/07/2008','UAE'

DELETE 
	D
FROM
	(
		SELECT 
			ROW_NUMBER() OVER (PARTITION BY Empno ORDER BY VacationDate DESC) AS RowNum
		FROM 
			@Table AS T
	) AS D
WHERE 
	RowNum > 1

You could also do it with a derived table (assuming no duplicate max dates)

DELETE
	T
FROM
	@Table AS T
INNER JOIN
(
	SELECT Empno, MAX(VacationDate) AS VacationDate
	FROM @Table
	GROUP BY Empno
) D
ON T.Empno = D.Empno
AND T.VacationDate < D.VacationDate

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-06 : 19:48:47

Thanks Very much Lampray

really thanks alot

Regards
dhani

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-07 : 11:59:33

Oh God,

This query is working only in sql server 2005 (Partition By Clause is not working in 2000, )

Can any one tell me how can i use in Sql server 2000 because it is

Please Help me

Thanks & regards
dhani

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-07 : 12:20:23

Try my alternate suggestion above.

dhani
Posting Yak Master

132 Posts

Posted - 2009-07-07 : 12:21:48

Thanks Lamprey,

i am using your Derived Table Concept, i didnt find the other derived query, this working like a charm

it is working alright

Thank you very much LAMPREY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources