Remove a record on max - 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 2008 Forums

Transact-SQL (2008)

Remove a record on max

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2011-07-15 : 10:44:23

i have a table with follwing fields and data

CustomerNumber , Name , EmployeeNumber, Points

79,John,12234,45
56,James,12234,56
23,Jake,14563,45

As you can see two customers have same employee number but i wnat only one of the two --the one with highest points and the the third record...how can i do that...thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 11:19:40

Since you are on SQL 2008, you can use the row_number function to do this. Run the following query - it will show you the rows that are going to be deleted. If you are satisfied that that is what you want to delete, comment out the last line that starts with select and uncomment the line above that starts with delete. Then run it, and it should delete those rows.

;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeNumber ORDER BY Points DESC) AS RN
	FROM YourTable
)
--DELETE FROM cte WHERE RN > 1;
SELECT * FROM cte WHERE RN > 1;

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2011-07-15 : 11:32:45

Thanks sunita...that helped

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources