Solved

sql server query

Posted on 2016-08-18
10
33 Views
Last Modified: 2016-08-18
I have a query that has given a clientcode where I know that there is a duplicate word in this example the word is 'BD'
I would like to delete those duplicates, I havent seen any with three or more duplicates but that could be possible, of course it must leave one of the words
not sure how to do a delete query to accomplish this
so this:

ClientCodeWordPosition      Word      clientcode      WordPosition
1B1AG_BD_1                               BD                1B1AG                     1
1B1AG_BD_2                               BD                1B1AG                     2
1B1AG_125_3                       125                1B1AG                     3
1B1AG_T_4                               T                1B1AG                     4

would become this
ClientCodeWordPosition      Word      clientcode      WordPosition
1B1AG_BD_1                               BD                1B1AG                     1
1B1AG_125_3                       125                1B1AG                     3
1B1AG_T_4                               T                1B1AG                     4

or this is fine too
ClientCodeWordPosition      Word      clientcode      WordPosition
1B1AG_BD_2                               BD                1B1AG                     2
1B1AG_125_3                       125                1B1AG                     3
1B1AG_T_4                               T                1B1AG                     4

ClientCodewordposition is the PK


here is the output

<dbo.TblWords ClientCodeWordPosition="1B1AG_BD_1" Word="BD" clientcode="1B1AG" WordPosition="1" />
<dbo.TblWords ClientCodeWordPosition="1B1AG_BD_2" Word="BD" clientcode="1B1AG" WordPosition="2" />
<dbo.TblWords ClientCodeWordPosition="1B1AG_125_3" Word="125" clientcode="1B1AG" WordPosition="3" />
<dbo.TblWords ClientCodeWordPosition="1B1AG_T_4" Word="T" clientcode="1B1AG" WordPosition="4" />

Open in new window


select query that pulled a known one out
use dictionary
select ClientCodeWordPosition, Word, clientcode, WordPosition
from dbo.TblWords
where clientcode ='1B1AG'
order by WordPosition

Open in new window

ee.JPG
0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41760693
quick try:
use dictionary
select ClientCodeWordPosition, Word, clientcode, WordPosition
from
(
select ClientCodeWordPosition, Word, clientcode, WordPosition,
  row_number() over (partition by word order by WordPosition ) idx
from dbo.TblWords
where clientcode ='1B1AG'
) a where idx = 1
order by WordPosition

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41760727
it worked but I dont know how and the duplicate words may not always be in the first position.

how does iot convert to a delete now

here is the output
1B1AG_BD_1          BD      1B1AG      1
1B1AG_125_3   125      1B1AG      3
1B1AG_T_4          T              1B1AG      4
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41760742
>>it worked but I dont know how and the duplicate words may not always be in the first position
it should work if there are duplicate words that may not be in first position. have you tried that?

>>how does iot convert to a delete now
what do you mean by that?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:PeterBaileyUk
ID: 41760753
I need to delete the duplicate rows
0
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41760763
try this:
use dictionary

Delete a from dbo.TblWords a
left join
(
	select ClientCodeWordPosition, Word, clientcode, WordPosition
	from
	(
		select ClientCodeWordPosition, Word, clientcode, WordPosition,
		  row_number() over (partition by word order by WordPosition ) idx
		from dbo.TblWords
		where clientcode ='1B1AG'
	) a where idx = 1
) b
on a.ClientCodeWordPosition = b.ClientCodeWordPosition
and a.Word = b.Word
and a.clientcode = b.clientcode
and a.WordPosition = b.WordPosition
where b.ClientCodeWordPosition is null

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41760775
thank you very much ryan
0
 

Author Comment

by:PeterBaileyUk
ID: 41760778
I just noticed it deleted all the rows in the word table as opposed to deleting the duplicated rows.
Is there a rollback command or shall i build the word table again?
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41760870
>>I just noticed it deleted all the rows in the word table as opposed to deleting the duplicated rows
it works fine for me before I posted the solution

>>Is there a rollback command or shall i build the word table again?
if you're not doing it in a SP with begin transaction clause, I'm afraid your records need to be created
0
 

Author Comment

by:PeterBaileyUk
ID: 41761019
it was an easy step to create the table again. do i just need to remove this part of the where so it just deletes the duplicate row
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41761050
>>do i just need to remove this part of the where so it just deletes the duplicate row
how's ur current Delete statement looks like to remove duplicate rows?
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question