Removing Duplicate - 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)

Removing Duplicate

Author

Topic

jamesingamells
Starting Member

11 Posts

Posted - 2013-10-23 : 08:08:35

Hi,
I have the below query that i need to remove some duplicates, but not quite as simple as it sounds.
What i would like to do is something like:
If [Read_code7],[Read_code] and[Staff_number] are the same then only include one row. The issue i have is that the caseload_holder and [Event_done_at] fields might have different values in them so i cant use a simple distinct.

Hopefully i have explained that well enough!

SELECT
[Patient_Count],
[Read_code7]
,[Read_code]
,[Current_Caseload_Holder]
,[Staff_number]
,[Event_date]
,[Event_done_at]

FROM table

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 08:11:46

[code]SELECT * FROM (
SELECT
[Patient_Count],
[Read_code7]
,[Read_code]
,[Current_Caseload_Holder]
,[Staff_number]
,[Event_date]
,[Event_done_at]
,ROW_NUMBER() OVER (
PARTITION BY [Patient_Count],[Current_Caseload_Holder],[Event_date],[Event_done_at]
ORDER BY [Patient_Count]) AS RN
FROM TABLE ) s WHERE RN=1;
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 08:38:17

shouldnt it be this?


SELECT * FROM (
SELECT
[Patient_Count],
[Read_code7]
,[Read_code]
,[Current_Caseload_Holder]
,[Staff_number]
,[Event_date]
,[Event_done_at]
,ROW_NUMBER() OVER (
	PARTITION BY [Read_code7],[Read_code],[Staff_number] ORDER BY [Patient_Count]) AS RN
FROM TABLE ) s WHERE RN=1;

as per below

If [Read_code7],[Read_code] and[Staff_number] are the same then only include one row

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jamesingamells
Starting Member

11 Posts

Posted - 2013-10-23 : 10:10:54

Many Thanks Visakh16 that seems to work a treat....is there anyway i can include which row it chooses? For example in some of the duplicated rows the[Current_Caseload_Holder] field is populated and then null. I would like to keep the populated field if possible?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 10:49:43

The order clause in the row_number determines which row is kept. So ORDER BY [Current_Caseload_Holder] DESC is perhaps what you need.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:07:41

quote:
Originally posted by jamesingamells

Many Thanks Visakh16 that seems to work a treat....is there anyway i can include which row it chooses? For example in some of the duplicated rows the[Current_Caseload_Holder] field is populated and then null. I would like to keep the populated field if possible?

if there are more than one rows populated with different values of [Current_Caseload_Holder] which one you want in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources