CODEWITHSUNDEEP
sqlsql-server
Jeet
Jeet

Reputation: 188

Is there a way in SQL Server to solve for this conditional problem for prioritization?

I have the following schema of a table

Name     Number
-----    -------
A           200
A           322
B           200
B           322
C           322
C           200
D           322
D           234

I need some conditional statement to add another label column.

  1. The conditions being that if a name has number 200, it should be prioritized over all other numbers and be labeled as 'Apple'
  2. The next condition is that if a name does not have a number 200, the second priority is for number 322. So then those should be labeled as 'Mango'

I want my final result to look something like this which is grouped by name. 

Name     Number     Label
-----    -------    ------
A          200       Apple
B          200       Apple
C          200       Apple
D          322       Mango

Upvotes: 0

Views: 65

Answers (2)

forpas
forpas

Reputation: 164089

With conditional aggregation:

select
  name,
  case min(case number when 200 then 0 when 322 then 1 end) 
    when 0 then 'Apple'
    when 1 then 'Mango'
  end Label 
from tablename
group by name

See the demo.
Results:

> name | Label
> :--- | :----
> A    | Apple
> B    | Apple
> C    | Apple
> D    | Mango

If you want the column Number also do the aggregation inside a CTE:

with cte as (
  select name, min(case number when 200 then 0 when 322 then 1 end) id
  from tablename
  group by name
)
select 
  name,
  case id when 0 then 200 when 1 then 322 end Number,
  case id when 0 then 'Apple' when 1 then 'Mango' end Label
from cte

See the demo.
Results:

> name | Number | Label
> :--- | -----: | :----
> A    |    200 | Apple
> B    |    200 | Apple
> C    |    200 | Apple
> D    |    322 | Mango

Upvotes: 3

Bestter
Bestter

Reputation: 878

You can do something like that:

SELECT (CASE WHEN [Number]=200 THEN 'APPLE' WHEN [Number] =322 THEN 'MANGO' ELSE 'WHATEVER' END) [Label], [Number]
FROM [Yourtablename]
ORDER BY (CASE WHEN [Number]=200 THEN 2 WHEN [Number] =322 THEN 1 ELSE 0 END) DESC

Upvotes: 1

Related Questions