MySQL: keep column together in results?

Question

OK when I make a request I want all the items with the same group_id to be "together" for example 117,117,134,111 is fine but 117,134,117,111 is not fine because the group_id 117 are not all "together". I hope that makes sense. The only way I know how to do achieve this is by ordering my results by group_id, but if I want to order by like price or something is there a way to do that while keeping all the matching group_id's together?

Thanks.

Answer 1

Yes, you just order by both, so your ORDER BY should look like this:

ORDER BY group_id, price

That will first order by group_id, then by price. So all the same group_ids will be together, but whenever there are multiple with the same group_id, they will be ordered by price.

Your question is a little ambiguous though, so just to explain in case it's what you actually wanted - there's no easy way to "mainly" order by price and just keep identical group_ids together, that doesn't really make any sense. What I mean is, if you had the following data:

group_id  price
117       2.00
117       5.00
111       4.00
134       1.00

You can't easily select it in this order:

group_id  price
134       1.00
117       2.00
117       5.00
111       4.00

Answer 2

Then you'd have to order by multiple columns, so:

SELECT group_id, price, name
FROM groups
ORDER BY group_id, price;

In this way, all the groupid's are together, and within those 'groups' of groupids, everything is sorted by it's price. Or have I misunderstood your question?

Answer 3

Why don't you order by price AND group_id???

SELECT price, group_id, ...
FROM products
WHERE 1=1
ORDER BY price DESC, group_id

This will show the biggest prices first, and for prices equal, it will show the group_id consecutive

Answer 4

I would do:

SELECT price, group_id
FROM groups
WHERE 1=1
ORDER BY group_id, price DESC

For showing groups consecutive, and then the prices in descendant order.