How to remove duplicates in SQL Server

Question

I have data looks like this:

Order No.  Name    Date   Unit Price  Freight
001        ABC     1-16    232         25
001        ABC     1-16    55          25
001        ABC     1-16    156         25
002        DEF     2-5     478         16
002        DEF     2-5     356         16

I am trying to let freight cost only show once in my table, the result would look like:

Order No.  Name    Date   Unit Price  Freight
001        ABC     1-16    232         25
001        ABC     1-16    55          0
001        ABC     1-16    156         0
002        DEF     2-5     478         16
002        DEF     2-5     356         0

Please help me with this

Current result: Order No. Name Date Unit Price Freight 001 ABC 1-16 232 25 001 ABC 1-16 55 25 001 ABC 1-16 156 25 002 DEF 2-5 478 16 002 DEF 2-5 356 16 — Zhen Liu
– Zhen Liu, Commented May 29, 2015 at 19:18
Looks like what you really need is to normalize your tables. You should have OrderHead and OrderDetail tables. The way you are doing this you are going to fight your denormalized structures forever. — Sean Lange
– Sean Lange, Commented May 29, 2015 at 19:32

shA.t · Accepted Answer · 2015-05-30 12:09:20Z

2

Here is a query to get what you want:

SELECT 
    order_no, name, theDate, unit_price, 
    case 
        when row_number() OVER (PARTITION by order_no ORDER BY order_no) = 1 then freight
        else 0 
    end as freight
from yourTable

This looks at all rows for each order number and provides the row number. If it's row 1 of that order it uses the values of the freight column, otherwise it uses 0.

Note that I'm assuming that the freight value is the same across all rows for the same order number.

edited May 30, 2015 at 12:09

shA.t

17k5 gold badges59 silver badges121 bronze badges

answered May 29, 2015 at 19:34

Mark Leiber

3,1382 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

sam yi Over a year ago

good answer... just one thing to note though (imo)... order by should be something other than order_no (i would do Date maybe) since everything within that partition will be the same anyways.

Mark Leiber Over a year ago

@samyi, I originally had date in the order by but decided to remove it since my assumption about the freight value being consistent across an order number made it redundant.

Zhen Liu Over a year ago

@MarkLeiber I'm sorry that I forgot to mention that my tables are actually join tables together, I don't think that partition will work if those are join tables.

Mark Leiber Over a year ago

It will still work. Do you want to edit your question to show the actual tables?

Zhen Liu Over a year ago

@MarkLeiber on my sql query, I also have lots of Case When statements.

|

Collectives™ on Stack Overflow

How to remove duplicates in SQL Server

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related