Aggregate functions - getting columns not included in the GROUP BY clause

Question

I have a table which has three fields (item_id, source, and price). (item_id, source, and price) together make up the primary key.

item_id    source      price
-------    -------     -----
1          paris       13
1          london      12
1          newyork     15
2          paris       22
2          london      15
2          newyork     14

For each item_id, I want to know the cheapest place to get it, and the price I will pay at that place. For the example above, I'd like to see the following output:

item_id    source     price
-------    -------    ---------
1          london     12
2          newyork    14

Is there a simple way to do this without mentioning the table name twice? I'm using MySQL, so I can't use a WITH clause.

I've taken a look at the joined two-query solution in another user's question (Group related records, but pick certain fields from only the first record), but I'd really like to avoid mentioning the table twice. The problem is that the simple table in this question actually represents an inline view in my real problem which takes about 5 seconds to create per use, so I'm hoping to find alternatives.

Answer 1

Here's one way, using an inner join to filter rows with the lowest price:

select i.* 
from items i
inner join (
    select item_id, min(price) as minprice
    from items 
    group by item_id
) cheapest on i.price = cheapest.minprice
    and i.item_id = cheapest.item_id

If there are multiple sources with the lowest price, they will all show up.

EDIT: Since your question mentions the items view takes long to build up, you could store the result in a temporary table.

create temporary table temp_items 
as 
select item_id, source, price from YourView

And then run the grouping query on the temporary table. In MySQL, a temporary table is visible only to the current connection, and is dropped automatically when the connection is closed.

Answer 2

I unfortunately don't know MySQL data and string conversion by heart. But here's something that if you massaged it a bit for the correct syntax might surprise you:

SELECT
   item_id,
   source = Convert(varchar(30), Substring(packed, 5, 30)),
   price = Convert(int, Substring(packed, 1, 4))
FROM
   (
      SELECT
         item_id,
         Min(Convert(binary(4), price) + Convert(varbinary(30), source) AS packed
      FROM items
      GROUP BY item_id
   ) X

This is surely a hack. It most definitely shouldn't be used in all cases. But when performance is critical, sometimes it's worth it.

See this thread for more info.