3

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

2

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
1
  • Wow ! Amazing thanks it does exactly what i want. Thank you for your quick help. Much appreciated :)
    – Scrodo
    Sep 15 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.