Solved

sql server query

Posted on 2016-08-18
10
31 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 50

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 50

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

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

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 50

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 50

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

785 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