Duplication Order Help - 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

General SQL Server Forums

New to SQL Server Programming

Duplication Order Help

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2015-03-19 : 17:33:32

Here is a table I have.

Q_ID ACCESS_KEY SUB_KEY ZIP ENTRY_DT
q123456 60000001245 0MKU786 46157 07/04/2057
q123456 60000001245 8EWGH65 46157 07/04/2057
q123456 60000001245 443CFP0 46157 07/04/2057

I want to retain one of the duplicated records. The one to keep is the one with SUB_KEY = 8EWGH65 as it is the last of the group. Is there a way to retain the last of a group then perform a LEFT OUTER JOIN on the same table to remove the unwanted duplicates? There'll be several instances of Q_ID. This is but a small sample. Thanks for any help!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-19 : 17:34:58

Is 8EWGH65 last in the group because of sorting it by char? I don't understand the LEFT JOIN question. Perhaps show more sample data and expected output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-19 : 18:14:16

Try this (but before you do the actual delete, do a "select *" to make sure, you get the duplicated you want to delete):

delete a
  from yourtable as a
       inner join (select *
                         ,row_number() over(partition by q_id,access_key,entry_dt order by sub_key desc) as rn
                     from  yourtable
                  ) as b
               on b.q_id=a.q_id
              and b.access_key=a.access_key
              and b.entry_dt=a.entry_dt
              and b.rn>1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources