Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server query

Posted on 2016-08-18
10
Medium Priority
?
35 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 53

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 53

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

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

Accepted Solution

by:
Ryan Chong earned 2000 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 53

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 53

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 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