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

  • 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-80SF0000018266

    ELLA WANG 74351 SF00008668 222-63-80SF0000018266

    JEFFRE VASCOE 74351 SF00008668 222-63-80SF0000018266

    UDITH YOUNG 74351 SF00008668 222-63-80SF0000018266

    5D TRUCKING 787051 869123 222-63-13SF0000015676

    A-1 Movers 70221 SF00012876 222-13-06SF0000017363

    A-1 Movers 70221 SF00012876 222-13-10SF0000017363

    A-1 Movers 70221 SF00012876 222-13-16SF0000017363

    Want:

    ANNA GAI 74351 SF00008668 222-63-80SF0000018266

    5D TRUCKING 787051 869123 222-63-13SF0000015676

    A-1 Movers 70221 SF00012876 222-13-06SF0000017363

  • 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-80SF0000018266

    ELLA WANG 74351 SF00008668 222-63-80SF0000018266

    JEFFRE VASCOE 74351 SF00008668 222-63-80SF0000018266

    UDITH YOUNG 74351 SF00008668 222-63-80SF0000018266

    5D TRUCKING 787051 869123 222-63-13SF0000015676

    A-1 Movers 70221 SF00012876 222-13-06SF0000017363

    A-1 Movers 70221 SF00012876 222-13-10SF0000017363

    A-1 Movers 70221 SF00012876 222-13-16SF0000017363

    Want:

    ANNA GAI 74351 SF00008668 222-63-80SF0000018266

    5D TRUCKING 787051 869123 222-63-13SF0000015676

    A-1 Movers 70221 SF00012876 222-13-06SF0000017363

    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,My blog: www.igormicev.com

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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,My blog: www.igormicev.com

  • 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. 😀 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • 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,My blog: www.igormicev.com

  • For some reason, my preview looks good but when I post it, everything looks garbled.

    I've sent an attachment. Thank you.

  • 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.

  • 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,My blog: www.igormicev.com

  • I must not be having good luck today. I've added it as a docx, and a .txt

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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,My blog: www.igormicev.com

  • Thank you.

  • Yes, I was try to remove duplicates based on Co_num. Thank you.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply