CODEWITHSUNDEEP
sqlsql-servergroup-by
Rupal
Rupal

Reputation: 480

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

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?

Upvotes: 7

Views: 9746

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460058

If you're using Sql-Server 2005 or greater you can use a CTE with DENSE_RANK function:

WITH CTE 
     AS (SELECT orderid, 
                name, 
                type, 
                price, 
                RN = Dense_rank() 
                       OVER ( 
                         PARTITION BY orderid 
                         ORDER BY CASE WHEN type='Beverage' THEN 0 ELSE 1 END ASC 
                         , price DESC) 
         FROM   dbo.tablename t 
         WHERE  EXISTS(SELECT 1 
                       FROM   dbo.tablename t2 
                       WHERE  t2.orderid = t.orderid 
                              AND type = 'Food') 
         AND    EXISTS(SELECT 1 
                       FROM   dbo.tablename t2 
                       WHERE  t2.orderid = t.orderid 
                              AND type = 'Beverage')) 
SELECT orderid, 
       name, 
       price 
FROM   CTE
WHERE  rn = 1

Use DENSE_RANK if you want all orders with the same highest price and ROW_NUMBER if you want one.

DEMO

Upvotes: 1

John Woo
John Woo

Reputation: 263693

Since you have tagged SQL Server, make use of Common Table Expression and Window Functions.

;WITH filteredList
AS
(
  SELECT OrderID
  FROM tableName
  WHERE Type IN ('Food','Beverage')
  GROUP BY OrderID
  HAVING COUNT(DISTINCT Type) = 2
),
greatestList
AS
(
    SELECT  a.OrderID, a.Name, a.Type, a.Price,
            DENSE_RANK() OVER (PARTITION BY a.OrderID
                                ORDER BY a.Price DESC) rn
    FROM tableName  a
          INNER JOIN filteredList b
              ON a.OrderID = b.OrderID
    WHERE a.Type = 'Beverage'
)
SELECT  OrderID, Name, Type, Price
FROM    greatestList
WHERE   rn = 1

Upvotes: 2

Taryn
Taryn

Reputation: 247670

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 |

Upvotes: 3

Related Questions