How to group data but taking rows only in a specific order (example should be clearer ;) )

Question

I have something i need to do and i don't know how to do it :)

I have the following data :

Art	Type
2215	Z
2215	C
2215	M
2216	C
2216

The result i want to have

Art	Type
2215	M
2216	C

Those data come from a query with a group by (let say it's the data from multiple store and some store have a different "Type" than other).

What i need to do is to be able to get only one row by "Art" but with the best "Type" ("Type" = to the cashback for a Customer)

I need to take the "Type" in that order

1. M
2. C
3. Z

How can i put it in my T-SQL statement ? I can still do something later in .NET but i would rather put everything in the SQL Statement.

Thank you for your help :)

Answer 1

You can apply a row number for each Art and explicitly specify that the order matches your preference:

;WITH src AS 
(
  -- this CTE represents your "query with a group by"
  SELECT Art, Type FROM 
  (
    VALUES
    (2215,  'Z'),
    (2215,  'C'),
    (2215,  'M'),
    (2216,  'C'),
    (2216,  '')
  ) AS v(Art, Type)
),
apply_rn AS
(
  SELECT Art, Type,
  rn = ROW_NUMBER() OVER (PARTITION BY Art ORDER BY CASE Type
    WHEN 'M' THEN 1
    WHEN 'C' THEN 2
    WHEN 'Z' THEN 3
    ELSE 4 END)
  FROM src
)
SELECT Art, Type 
  FROM apply_rn 
  WHERE rn = 1
  ORDER BY Art;

Results:

Art    Type
----   ----
2215   M
2216   C

• Example db<>fiddle