SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete duplicate row based on one column, but need to retain rest of the values in tmp file


Delete duplicate row based on one column, but need to retain rest of the values in tmp file

Author
Message
Gillian_Pappas2002
Gillian_Pappas2002
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 122
Hi.

I have the following code which is correctly selecting my data, but it is giving me duplicates and I can't use a distinct, or Max to be able to eliminate the duplicates. Any ideas?
Thanks for all of your help.

G

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,([order_date]) as order_date
,MAX ([promise_date]) as promise_date
,[AvailableDate]
,MAX ([inv_date]) as inv_date
INTO #rptset
FROM @tmp_FullfillOrder
GROUP BY
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]
--9 Days to available - number of days between order date and date aXllocated.
,DATEDIFF(day,[order_date],[availabledate]) as 'DaysToAvailable'
--10 Ship days - number of days from order date to highest invoice date
,DATEDIFF(day,order_date,inv_date) as 'ShipDays'
--11 Reliable? - Yes if allocate date is less than or equal to Orig Promise date, otherwise leave blank
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
then 'Yes' else 'No' end as 'Reliable'
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
THEN 1 ELSE 0 end as 'ReliableCalc'

FROM #rptset








Partial Output:
name cust_num co_num item inv_num
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
ELLA WANG 74351 SF00008668 222-63-80 SF0000018266
JEFFRE VASCOE 74351 SF00008668 222-63-80 SF0000018266
UDITH YOUNG 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363
A-1 Movers 70221 SF00012876 222-13-10 SF0000017363
A-1 Movers 70221 SF00012876 222-13-16 SF0000017363

Want:
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5890 Visits: 5080
Gillian_Pappas2002 (12/18/2013)
Hi.

I have the following code which is correctly selecting my data, but it is giving me duplicates and I can't use a distinct, or Max to be able to eliminate the duplicates. Any ideas?
Thanks for all of your help.

G

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,([order_date]) as order_date
,MAX ([promise_date]) as promise_date
,[AvailableDate]
,MAX ([inv_date]) as inv_date
INTO #rptset
FROM @tmp_FullfillOrder
GROUP BY
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]

SELECT
[name]
,[cust_num]
,[co_num]
,[item]
,[inv_num]
,[order_date]
,[promise_date]
,[AvailableDate]
,[inv_date]
--9 Days to available - number of days between order date and date aXllocated.
,DATEDIFF(day,[order_date],[availabledate]) as 'DaysToAvailable'
--10 Ship days - number of days from order date to highest invoice date
,DATEDIFF(day,order_date,inv_date) as 'ShipDays'
--11 Reliable? - Yes if allocate date is less than or equal to Orig Promise date, otherwise leave blank
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
then 'Yes' else 'No' end as 'Reliable'
,CASE WHEN
DATEDIFF(day, ISNULL(availabledate,'1/1/1900'), ISNULL(promise_date,'1/1/1900'))>=0
THEN 1 ELSE 0 end as 'ReliableCalc'

FROM #rptset








Partial Output:
name cust_num co_num item inv_num
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
ELLA WANG 74351 SF00008668 222-63-80 SF0000018266
JEFFRE VASCOE 74351 SF00008668 222-63-80 SF0000018266
UDITH YOUNG 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363
A-1 Movers 70221 SF00012876 222-13-10 SF0000017363
A-1 Movers 70221 SF00012876 222-13-16 SF0000017363

Want:
ANNA GAI 74351 SF00008668 222-63-80 SF0000018266
5D TRUCKING 787051 869123 222-63-13 SF0000015676
A-1 Movers 70221 SF00012876 222-13-06 SF0000017363





Hi
Try this code:

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

select * from #rptset



Regards,
IgorMi

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26008 Visits: 17528
From the sample output you posted you don't have any duplicates. There are unique values in one of those columns (I can't tell from the garbled display which one). What are the rules about deciding which row to return?

I will be happy to help but you need to first help me. Take a few minutes and read the first article in my signature about best practices when posting questions.

_______________________________________________________________

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 Modens 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)
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5890 Visits: 5080
Sean Lange (12/18/2013)
From the sample output you posted you don't have any duplicates. There are unique values in one of those columns (I can't tell from the garbled display which one). What are the rules about deciding which row to return?

I will be happy to help but you need to first help me. Take a few minutes and read the first article in my signature about best practices when posting questions.


I think he was asking for duplicates based on inv_num.

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26008 Visits: 17528
IgorMi (12/18/2013)
Sean Lange (12/18/2013)
From the sample output you posted you don't have any duplicates. There are unique values in one of those columns (I can't tell from the garbled display which one). What are the rules about deciding which row to return?

I will be happy to help but you need to first help me. Take a few minutes and read the first article in my signature about best practices when posting questions.


I think he was asking for duplicates based on inv_num.


Yeah you posted at the same time I did. Once I saw your code the light bulb turned on. :-D Gotta love that we have lots of people around here. At least somebody was able to figure out what they wanted.

_______________________________________________________________

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 Modens 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)
Gillian_Pappas2002
Gillian_Pappas2002
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 122
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
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5890 Visits: 5080
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


You have to identify what is unique in your data and/or and what columns to use in order to determine rows for deleting.

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
Gillian_Pappas2002
Gillian_Pappas2002
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 122
For some reason, my preview looks good but when I post it, everything looks garbled.

I've sent an attachment. Thank you.
Attachments
Gillian_Pappas2002
Gillian_Pappas2002
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 122
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.
Attachments
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5890 Visits: 5080
Gillian_Pappas2002 (12/18/2013)
For some reason, my preview looks good but when I post it, everything looks garbled.

I've sent an attachment. Thank you.


Cannot open the attachment...

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search