Mysql Multiple Group Bys, order by one group by item ?

Question

Ok, So I have a table called 'auctions' and each auction has a 'product_id' associated.

The auctions table may have 500 items with repeated products.

I want to order by auction_id asc (so newer auctions are shown first), but also only show distinct products. Using distinct doesn't work, and when I group by the product_id, it doesn't order the auctions properly.. Here's what I have

SELECT product_id, auctions.id auctions
INNER JOIN products ON auctions.product_id = products.id
INNER JOIN product_groups ON products.group_id = product_groups.id
INNER JOIN product_images on products.id = product_images.product_id
WHERE products.group_id = '1'
GROUP BY products.id
ORDER BY auctions.id ASC
LIMIT 10

In this example, it does show the unique products, however the order is not correct... if I add auctions.id to the group by, the order is correct, but there are multiple products..

Any help would be awesome - thank you!

Answer 1

You are not using GROUP BY correctly. You have to use an agregate function on auctions.id if you want the query to be valid, such as MIN to retrieve the smallest auctions.id for every product_id group :

SELECT product_id, MIN(auctions.id) auctions
INNER JOIN products ON auctions.product_id = products.id
INNER JOIN product_groups ON products.group_id = product_groups.id
INNER JOIN product_images on products.id = product_images.product_id
WHERE products.group_id = '1'
GROUP BY products.id
ORDER BY auctions.id ASC
LIMIT 10

P.S. You can use alias for your tables in your query which would shorten it.