Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql server query

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

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 55

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

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 55

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 55

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

578 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