SQL - how to GROUP by id and identify the column with highest value?

Question

I have a SQL challenge which I need a little help with.

Below is a simplified example, in my real case I have about 500k rows in a slow VIEW. So if you have a solution that is effective as well, I would appreciate it. I'm thinking I have to use GROUP BY in one way or another, but I'm not sure.

Let's say I have a table like this

╔═════════╦══════════╦══════════╦═══════╗
║ ORDERID ║   NAME   ║   TYPE   ║ PRICE ║
╠═════════╬══════════╬══════════╬═══════╣
║       1 ║ Broccoli ║ Food     ║ 1     ║
║       1 ║ Beer     ║ Beverage ║ 5     ║
║       1 ║ Coke     ║ Beverage ║ 2     ║
║       2 ║ Beef     ║ Food     ║ 2.5   ║
║       2 ║ Juice    ║ Beverage ║ 1.5   ║
║       3 ║ Beer     ║ Beverage ║ 5     ║
║       4 ║ Tomato   ║ Food     ║ 1     ║
║       4 ║ Apple    ║ Food     ║ 1     ║
║       4 ║ Broccoli ║ Food     ║ 1     ║
╚═════════╩══════════╩══════════╩═══════╝

So what I want to do is:

In each order, where there are BOTH food and beverage order line, I want the highest beverage price

So in this example i would like to have a result set of this:

╔═════════╦═══════╦═══════╗
║ ORDERID ║ NAME  ║ PRICE ║
╠═════════╬═══════╬═══════╣
║       1 ║ Beer  ║ 5     ║
║       2 ║ Juice ║ 1.5   ║
╚═════════╩═══════╩═══════╝

How can I acheive this in an effective way?

Answer 1

You can use the a subquery that gets the max(price) for each order with both food and beverage and then join that back to your table to get the result:

select t1.orderid,
  t1.name,
  t1.price
from yourtable t1
inner join
(
  select max(price) MaxPrice, orderid
  from yourtable t
  where type = 'Beverage'
    and exists (select orderid
                from yourtable o
                where type in ('Food', 'Beverage')
                  and t.orderid = o.orderid
                group by orderid
                having count(distinct type) = 2)
  group by orderid
) t2
  on t1.orderid = t2.orderid
  and t1.price = t2.MaxPrice

See SQL Fiddle with Demo

The result is:

| ORDERID |  NAME | PRICE |
---------------------------
|       1 |  Beer |     5 |
|       2 | Juice |   1.5 |