Delete duplicate based on one column based on another column's value

Question

I have a table with data like

OrderId	Code	Val
1	Code1	abc
2	Code2	def
3	Code1	aaa
3	Code2	bbb

Expected

OrderId	Code	Val
1	Code1	abc
2	Code2	def
3	Code2	bbb

Now, I want to get the OrderId in such a way that, OrderId should have only one Code value. If there are more than one Code value for an OrderId then Code2 takes priority. So my final result should look like, OrderId 3 should have only one record with Code2 as value, how to query this in T-SQL. I tried with following query but not sure how to proceed after that

select OrderId, Code, count(*)
from Table1
group by OrderId, Code

@Dale K: The question which you linked as duplicate of this one (or vice versa) is another scenario (and therefore the answers of that question, too). The other question tells us how to fetch all records except a specific one. This question here is quite the opposite case, i.e. the goal is not to fetch all records except one, but to fetch one record and exclude all others. Therefore, the linked question and their answers will not solve this question. — Jonas Metzler, Jan 19 '23 at 05:40
@JonasMetzler same principle though, but yeah not exact. Still there will be a duplicate out there which I would suggest you find rather than answering the same thing yet again. — Dale K, Jan 19 '23 at 06:06
The general concept of finding the highest value per group has been answered many times, yes. I guess this specific case here can be tricky due to the "mixed" codes because you likely need to use SUBSTRING (or a similar function) to fetch the highest number from the code and to prevent Code2 will be chosen and not Code10. Maybe there is even a duplicate question for that case, I don't know. — Jonas Metzler, Jan 19 '23 at 06:26

score 3 · Answer 1 · edited Jan 19 '23 at 05:28

3

You can use concept of ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY OrderCode desc) row_num

e.g.

select *
from (
    select *
        , row_number() over (partition by orderId order by ordercode desc) row_num
    from #OrderTemp
) Orders
where row_num = 1

edited Jan 19 '23 at 05:28

Dale K

25,246
15
42
71

answered Jan 19 '23 at 05:13

Kishlay Bhagwan

31
2

Jonas Metzler · Accepted Answer · 2023-01-19T06:42:37.203

The answer to your original question before you edited it was this simple query with MAX with GROUP BY:

SELECT 
    OrderId, 
    MAX(Code) AS Code
FROM yourtable
GROUP BY OrderId
ORDER BY OrderId;

If according to your new requirements further columns should be selected, we could use the above query as subquery with JOIN:

SELECT 
    y.OrderId, 
    y.Code, 
    y.Val
FROM yourtable y
INNER JOIN (
    SELECT 
        OrderId, 
        MAX(Code) AS Code
    FROM yourtable
    GROUP BY OrderId
) AS sub ON y.OrderId = sub.OrderId
    AND y.Code = sub.Code
ORDER BY y.OrderId;

But this becomes long and bad to read. Therefore using a window function should be prefered.

But there is another possible issue which should be solved:

We should be careful with this simple MAX or ROW_NUMBER ideas if lots of codes per OrderId can appear because the above query will for example fetch Code2, not Code10 as highest code if both appear. That's because it's a string, not a number.

I guess that's not intended. We can fix this issue by finding out the highest number after the word code. So we could do something like this here, using SUBSTRING and ROW_NUMBER:

SELECT orderId, code, val
FROM (
  SELECT 
    orderId, code, val,
    ROW_NUMBER() OVER 
      (PARTITION BY orderId 
      ORDER BY CAST(SUBSTRING(Code,5,LEN(code)-4) AS INT) DESC) row_num
    FROM yourtable
) Orders
WHERE row_num = 1;

Thus, Code10 will be taken rather than Code2.

We can replicate these things here: db<>fiddle

The idea with the longer query is also shown in the fiddle, but as said this is unhandy and bad to read, so I don't recommend it.

Thanks for the reply, just curious is there any other way without using max , because I want other columns also, while using aggregate will not be able to use other columns. Apologize for the trouble — Annas, Jan 19 '23 at 05:06

Delete duplicate based on one column based on another column's value

2 Answers2