Solved

sql server query

Posted on 2016-08-18
10
30 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
  • 5
  • 5
10 Comments
 
LVL 49

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 49

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
 

Author Comment

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

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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 49

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 49

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now