I am trying to get an SQL statement that will output a unique part number eg no duplicates However I want the type as Purchased is the default and when there isnt a Purchased part it defults back to Manufactured. NOTE all parts can be purchased
The result I require is to only show unique part numbers e.g. 1 to 10 and Purchased is the default Type
Table1
Part_number | Type | Cost Part 1 | Manufactured | £1.00 Part 1 | Purchased | £0.56 Part 2 | Manufactured | £1.26 Part 2 | Purchased | £0.94 Part 3 | Manufactured | £0.36 Part 3 | Purchased | £0.16 Part 4 | Manufactured | £1.00 Part 4 | Purchased | £1.50 Part 5 | Manufactured | £1.65 Part 6 | Manufactured | £1.98 Part 7 | Manufactured | £0.15 Part 8 | Manufactured | £0.45 Part 9 | Manufactured | £1.20 Part 9 | Purchased | £0.80 Part 10| Manufactured | £1.00
This is the result I am hoping to get back
Part_number | Type | Cost Part 1 | Purchased | £0.56 Part 2 | Purchased | £0.94 Part 3 | Purchased | £0.16 Part 4 | Purchased | £1.50 Part 5 | Manufactured | £1.65 Part 6 | Manufactured | £1.98 Part 7 | Manufactured | £0.15 Part 8 | Manufactured | £0.45 Part 9 | Purchased | £0.80 Part 10| Manufactured | £1.00
I have tried loads of different techniques but am not getting the result.
I am guessing that I will need to create temp tables that are filtered and then join the tables together but I really don’t know.
Any help will be apricated
Advertisement
Answer
You could also just grab the first row in each group by sorting them. This would make it easier when there are other columns of data to bring back.
with data as (
select *, row_number() over (
partition by part_number
order by case when t.type = Purchased then 1 else 2 end) as rn
from t
)
select * from data where rn = 1;
If there are other types this would work as well although you would want to tweak it if there are more than two per part.