MySQL sort column by values

Question

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

fthiella · Accepted Answer · 2015-09-22 09:57:57Z

2

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.

answered Sep 22, 2015 at 9:57

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Timo002 Over a year ago

This did the job! I forgot the DESC and I need to add the ID's in reverse order! Thanx! Was looking at it but just didn't understand what was going wrong!

Gordon Linoff · Accepted Answer · 2015-09-22 10:51:00Z

1

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.

answered Sep 22, 2015 at 10:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL sort column by values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related