How to GROUP by id and identify the column with the highest value?

Question

I have an SQL call that I need a little help with.

The following is a simplified example, in my real case I have about 500 thousand lines in slow VIEW. Therefore, if you have an effective solution, I would appreciate it. I think I should use GROUP BY anyway, but I'm not sure.

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:

In every order where there is a USUAL line of food and drink orders, I want the highest price for a drink

So, in this example, I would like to get a result set from this:

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

How can I do this effectively?

Answer 1

Since you checked SQL Server , use 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 

• SQLFiddle Demo

Answer 2

You can use a subquery that gets max(price) for each order with food and drinks, and then joins this 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

Result:

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

Answer 3

This is a relational division: link 1 , link 2 .

If the divider table (only food and drink ) is static, you can use one of these solutions:

 DECLARE @OrderDetail TABLE ([OrderID] int, [Name] varchar(8), [Type] varchar(8), [Price] decimal(10,2)) ; INSERT INTO @OrderDetail ([OrderID], [Name], [Type], [Price]) SELECT 1, 'Broccoli', 'Food', 1.0 UNION ALL SELECT 1, 'Beer', 'Beverage', 5.0 UNION ALL SELECT 1, 'Coke', 'Beverage', 2.0 UNION ALL SELECT 2, 'Beef', 'Food', 2.5 UNION ALL SELECT 2, 'Juice', 'Beverage', 1.5 UNION ALL SELECT 3, 'Beer', 'Beverage', 5.0 UNION ALL SELECT 4, 'Tomato', 'Food', 1.0 UNION ALL SELECT 4, 'Apple', 'Food', 1.0 UNION ALL SELECT 4, 'Broccoli', 'Food', 1.0 -- Solution 1 SELECT od.OrderID, COUNT(DISTINCT od.Type) AS DistinctTypeCount, MAX(CASE WHEN od.Type='beverage' THEn od.Price END) AS MaxBeveragePrice FROM @OrderDetail od WHERE od.Type IN ('food', 'beverage') GROUP BY od.OrderID HAVING COUNT(DISTINCT od.Type) = 2 -- 'food' & 'beverage' -- Solution 2: better performance SELECT pvt.OrderID, pvt.food AS MaxFoodPrice, pvt.beverage AS MaxBeveragePrice FROM ( SELECT od.OrderID, od.Type, od.Price FROM @OrderDetail od WHERE od.Type IN ('food', 'beverage') ) src PIVOT ( MAX(src.Price) FOR src.Type IN ([food], [beverage]) ) pvt WHERE pvt.food IS NOT NULL AND pvt.beverage IS NOT NULL 

Results (for solutions 1 and 2):

 OrderID DistinctTypeCount MaxBeveragePrice ----------- ----------------- --------------------------------------- 1 2 5.00 2 2 1.50 Table 'Worktable'. Scan count 2, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#09DE7BCC'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. OrderID MaxFoodPrice MaxBeveragePrice ----------- --------------------------------------- --------------------------------------- 1 1.00 5.00 2 2.50 1.50 Table '#09DE7BCC'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

Answer 4

If you use Sql-Server 2005 or higher, you can use the CTE function with DENSE_RANK :

 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 maximum price and ROW_NUMBER if you want it.

DEMO