CODEWITHSUNDEEP
sqlsql-servercase-when
M. Straw
M. Straw

Reputation: 502

How can I set priority using Case-When in SQL

For a project at work, I need to be able to prioritize the version of a magazine a customer gets.

If someone is qualified for more than one version of the brochure because of the categories they bought from, here is the priority:

  1. Women's
  2. Men's
  3. Media
  4. Best of

So, if someone bought a women's sweater, a DVD, a birdfeeder and men's shoes, they would get the women's brochure. I'm pretty sure a Case/When statement would be used, but I'm not 100% on that.

Thanks for your help!

Upvotes: 1

Views: 5731

Answers (2)

Serg
Serg

Reputation: 22811

You need a table which assigns priority to category

select * 
into #categories
from(
    values
        (4,'Women''s'),
        (3,'Men''s'  ),
        (2,'Media'   ),
        (1,'Best of' )
    ) t(priority, title);

And now every customer gets the title of the higest priority he bouhgt.

select customer
  , title = (select title 
     from #categories c2
     where max(c.priority) = c2.priority)
from ( -- sample data
    values
        (10,'Women''s'),
        (10,'Men''s'),
        (10,'Media'  ),
        (10,'Best of' ),
        (20,'Men''s'),
        (20,'Best of' )
    ) tbl(customer, category)
join #categories c
    on c.title=tbl.category
group by customer

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Use CASE expression

SELECT 
col,
CASE    
    WHEN col='Women''s' then 1
    WHEN col='Men''s' then 2
    WHEN col='Media' then 3
    WHEN col='Best of' then 4
END as priority
FROM TABLE

Upvotes: 4

Related Questions