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

Answer 1

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.