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

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

• M

• C

• 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 :)

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

StackExchange Database Administrators Q#299628, answer score: 3