MySQL Specific ORDER BY Clause
My MySQL database contains multiple products stored in 4 categories. All information is in one table:
id - name - description - price - category
I am trying to list them in this order. I'm interested in doing this only on a MySQL query, if possible.
Cat1 : product-name-with-lower-price1
Cat2 : product-name-with-lower-price1
Cat3 : product-name-with-lower-price1
Cat4 : product-name-with-lower-price1
Cat1 : product-name-with-lower-price2
Cat2 : product-name-with-lower-price2
Cat3 : product-name-with-lower-price2
Cat4 : product-name-with-lower-price2
Cat1 : product-name-with-lower-price3
Cat2 : product-name-with-lower-price3
Cat3 : product-name-with-lower-price3
Cat4 : product-name-with-lower-price3
etc...
I wish I had tested the source code because everything I tried doesn't work. I think I should use the MySQL Order By clause, but I don't know how.
Many thanks
EDIT:
Products are listed at a lower price. After retrieving the lower price of each cat, we order at the ASC price. Then fetch the next lower price of each cat and order at ASC price and so on ...
So it might look like this:
Cars : Aygo 8000$
Moto : Suzuki 8200$
Bus : Renault 8700$
Truck : Peugeot 9000$
Cars : Toyota 9300$
Bus : Renault 9400$
Truck : DMG 9600$
Moto : Harley 14000$
Bus : Mercedes 12000$
Moto : BMW : 18000$
Cars : Mercedes 11000$
Truck : Renault 10000$
source to share
This should be an improvement on Raina's approach. There are two questions with this approach. The first is order by
in a subquery. There is no guarantee that ordering in a subquery will yield results in the same order (it usually works in practice in MySQL, but you shouldn't rely on behavior). The second has multiple variable assignments in select
. MySQL does not guarantee the order of assignments.
A safer way to write:
SELECT id, price, category
FROM (SELECT (@rownum := IF(@cat = t.category, @rownum + 1,
IF(@cat := t.category, 1, 1)
)
)criteria,
t.*
FROM Table1 t
ORDER BY category, price
) tt
ORDER BY tt.criteria, tt.category;
source to share
One possible approach:
SELECT id, price, category
FROM (
SELECT @rownum :=
(CASE
WHEN @cat = t.category THEN @rownum + 1
ELSE (@cat := t.category) IS NOT NULL
END) criteria,
t.*
FROM Table1 t
ORDER BY category, price
) tt
ORDER BY tt.criteria,
FIELD(tt.category, 'Cat1', 'Cat2', 'Cat3');
SQL Fiddle . The function isFIELD
used to customize a certain order of categories; if you're ok with them sorted alphabetically, you can only replace it withORDER BY tt.criteria, tt.category
.
source to share