Ordering by multiple columns in MYSQL

Question

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 `products`.`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

Ah - my mistake in simplifying my SQL for the question. It is there in the real thing! — Mateo, Mar 22 '11 at 12:27

score 0 · Answer 1 · answered Mar 22 '11 at 11:54

0

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.

answered Mar 22 '11 at 11:54

Arun

3,036
3
35
57

Hi Arun, thanks for your suggestion. This didn't work - it returned rows with duplicate area_ids. – Mateo Mar 22 '11 at 12:41
If you want just the areaid and the minimum price, that can be done by selecting the area and min(price) alone. But if an area has different product dates, durations, ratings, etc. You'll have to decide which of those you want. For example you can SELECT areaid, min(price), max(ratings), sum(durations).... – Arun Mar 22 '11 at 12:57

Nicola Cossu · Accepted Answer · 2011-03-22T12:42:43.093

0

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);

edited Mar 22 '11 at 12:42

answered Mar 22 '11 at 11:56

Nicola Cossu

54,599
15
92
98

Thanks a lot, Nick - this works. It is quite slow on my database (10-12 seconds on my 85k rows now, will be ~750k rows when live), though. Is there any faster alternative? – Mateo Mar 22 '11 at 12:37
@Mateo. Add an index to your table on (area_id,price) – Nicola Cossu Mar 22 '11 at 12:40
Can you tell us about performance after you've added the index? – Nicola Cossu Mar 22 '11 at 13:34
@nick Select without index: 4.9s. After index: 0.0087s. Huge difference. Thanks. – Mateo Mar 22 '11 at 15:03

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

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

edited May 23 '17 at 12:11

Community

1
1

answered Mar 22 '11 at 12:17

xkeshav

53,360
44
177
245

Hi, this created some kind of (endless?) loop - the query never finished. Edit: actually this comment refers to your pre-edit solution - am trying your updated solution now. – Mateo Mar 22 '11 at 12:41
i have tries on my mysql, it works fine. remove `ORDER BY` CLAUSE completely – xkeshav Mar 22 '11 at 12:43

Ordering by multiple columns in MYSQL

3 Answers3