CODEWITHSUNDEEP
mysql
Timo002
Timo002

Reputation: 3208

MySQL sort column by values

I want to sort my database result based on a column value. I thought I needed to do this:

SELECT * FROM products
ORDER BY FIELD(brand_id, 4, 1, 6)

But this doesn't sort my result at all. Or at least in a way I don't understand.

What I want to achieve is that I get all products, ordered by brand_id. First all with brand_id 4, then 1 then 6 and then the rest.

Here is my example on jsfiddle.

If not working (jsfiddle not working fine here), see my code below:

Schema:

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `brand_id` int(10) unsigned NOT NULL,
  `price` decimal(8,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `products` (`id`, `brand_id`, `price`)
VALUES
    (1, 1, 10.32),
    (2, 4, 15.32),
    (3, 2, 23.32),
    (4, 6, 56.32),
    (5, 4, 23.32),
    (6, 4, 23.32),
    (7, 1, 25.32),
    (8, 5, 15.32),
    (9, 3, 55.32),
    (10, 5, 23.32);

Upvotes: 1

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Normally, when you use field() for sorting you also have in:

SELECT p.*
FROM products p
WHERE brand_id IN (4, 1, 6)
ORDER BY FIELD(brand_id, 4, 1, 6);

Then it works as advertised.

Instead of reversing values, you can also take a more explicit approach:

SELECT p.*
FROM products p
ORDER BY brand_id IN (4, 1, 6) DESC,
         FIELD(brand_id, 4, 1, 6);

This is longer, but the intention is clear.

Upvotes: 1

fthiella
fthiella

Reputation: 49049

The problem is here:

 FIELD(brand_id, 4, 1, 6)

will return 0 if brand_id is not present in the list, so brands not present will be at the top. You can use this:

ORDER BY FIELD(brand_id, 6, 1, 4) DESC

with all of the values in reverse order, and then you have to order in DESC order.

Upvotes: 2

Related Questions