2

I'm quite new to SQL, so it might be a noob question:

Let's say my query is:

select [Item], [Answers] from table1

And it retrieves the following table:

Item     Answers
------------------
Car      Expensive
Car      Cheap
Car      Medium
Boat     Expensive
Boat     Very Expensive
Boat     Ultra Expensive

Given a certain second table (or dictionary, I don't really know how to do it) {Cheap: 1, Medium: 2, Expensive: 3, Very Expensive:4, Ultra Expensive: 5} - meaning that "Ultra Expensive" is the highest rank and "Cheap" is the lowest rank.

In SQL, on this kind of table with many duplicates in Column A (Item) how do I retrieve the highest ranked value in Column B (Answers) for each unique value in Column A?

In this example, I would like to get:

Item     Answers
------------------
Car      Expensive
Boat     Ultra Expensive

Just one of each duplicated value in column 'Item' and its highest ranked possible value in 'Answers'?

GMB
  • 216,147
  • 25
  • 84
  • 135
SmileyVitaminC
  • 313
  • 2
  • 7
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – EzLo Oct 31 '19 at 14:02

1 Answers1

4

You can use a correlated subquery:

select item, answer
from table1 t
where answer = (
    select top 1 answer
    from table1 t1
    where t1.item = t.item
    order by
        case answer
            when 'Cheap' then 1
            when 'Medium' then 2
            when 'Expensive' then 3
            when 'Very Expensive' then 4
            when 'Ultra Expensive' then 5
        end desc
)

The subquery filters on the answer that has the highest rank for the given item, using a conditional order by clause and top 1.

Another option is to use row_number() for filtering:

select item, answer 
from (
    select 
        item, 
        answer,
        row_number() over(
            partition by item 
            order by case answer
                when 'Cheap' then 1
                when 'Medium' then 2
                when 'Expensive' then 3
                when 'Very Expensive' then 4
                when 'Ultra Expensive' then 5
            end desc
        ) rn
    from table1
) t
where rn = 1
GMB
  • 216,147
  • 25
  • 84
  • 135