Ordering by multiple columns in MYSQL

I am building a small app that shows the cheapest price for a product by area. I need the result from my database to show only one result per area, and that result must have the lowest price of all rows for that area.

I've come up with this so far, which is nearly there but mixes up the result rows.

SELECT `pr开发者_JS百科oducts`.`id`, `products`.`area_id`, `products`.`date`, 
`products`.`duration`, MIN(`products`.`price`) AS `price`, `products`.`rating`,
`products`.`buy_url` FROM `products` WHERE `price` >= '0' GROUP BY `products`.`area_id` ORDER BY 
`price` ASC

Although this successfully returns only one result per area, using MIN() here seems to get the lowest price for that area, but the other columns will be from a different row (i.e. the row that would have been selected in its entirety had I not used MIN() above).

So, I obviously have this wrong. I'd be really grateful for some advice on how I can select the lowest 'price', along with the rest of that row, from each distinct area.

Thanks,

Matt

select t1.* from products as t1
inner join (
select area_id,min(price) as price
from products where price > 0
group by area_id) as t2
on t1.area_id = t2.area_id and t1.price = t2.price


alter table products add index i (area_id,price);

SELECT `products`.`id`, `products`.`area_id`, `products`.`date`, `products`.`duration`, MIN(`products`.`price`) AS `price`, `products`.`rating`, `products`.`buy_url` WHERE `price` >= '0' GROUP BY `products`.`id`, `products`.`area_id`, `products`.`date`, `products`.`duration`,  `products`.`rating`, `products`.`buy_url` ORDER BY `price` ASC

You'll have to group by all the columns you are selecting.

what about this

SELECT MIN(p1.price) AS minPrice, 
p1.id, p1.area_id, p1.date, p1.duration,p1.rating,p1buy_url 
FROM products p1
LEFT JOIN products p2
ON (p1.area_id=p2.area.id AND p1.id<p2.id)
WHERE p2.id is NULL
GROUP BY area_id 
ORDER BY p1.id ASC

note: you can not order by on a field( here u mention minPrice) which is not exist in table

LEFT JOIN is faster than INNER JOIN as u can check by using EXPLAIN keyword before SELECT

Reference Question