Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Delete duplicate row based on one column, but need to retain rest of the values in tmp file Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 8:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
I must not be having good luck today. I've added it as a docx, and a .txt

  Post Attachments 
Remove duplicate rows based on co_num, but retain the rest of the tmp record.docx (1 view, 15.15 KB)
duplicates.txt (4 views, 1.90 KB)
Post #1524161
Posted Wednesday, December 18, 2013 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
Gillian_Pappas2002 (12/18/2013)
My posts look fine in preview then get messed up when I post it. Sorry. I have included an attachment.

Thanks for all of your help.


Displaying tabular information is not something html is good at. That is why there are html tables. UGH!!! It is generally best to just create a temp table for your results. It only takes a few more seconds.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1524166
Posted Wednesday, December 18, 2013 8:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 2,934, Visits: 2,959
Gillian_Pappas2002 (12/18/2013)
Thank you so much. That worked really well, but for some reason, there remain a few randon duplicate co_nums rows showing up. But I truly appreciate your help. Thanks. G


Hi,

Pay attention on this fragment of the above posted code:
OVER(PARTITION by inv_num ORDER BY name)

You've decided to remove duplicates based on inv_num and here you're telling us about some co_nums.
You can also specify more columns after PARTITION BY... as well as in after ORDER BY..
I think you'll manage to settle up your results.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1524170
Posted Wednesday, December 18, 2013 8:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thank you.
Post #1524178
Posted Wednesday, December 18, 2013 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Yes, I was try to remove duplicates based on Co_num. Thank you.
Post #1524179
Posted Wednesday, December 18, 2013 8:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thank you so very much. For some reason when I moved everything from the #temp table over to a @temp table and applied your logic....

;WITH cte as
(
SELECT *,
ROW_NUMBER() OVER(PARTITION by co_num ORDER BY name) AS dupCnt
FROM @tmp_FullfillOrder2
)
--Now Delete Duplicate Records
DELETE FROM cte
WHERE dupCnt > 1

select * from @tmp_FullfillOrder2


Dupicates were removed, but all of the rest of my calculations, etc., were intact. I can't thank you enough for all of your help and all of the great people who help us here at SqlServerCentral. You all are SUPER. Thank you again.

Gillian
Post #1524189
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse