CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 20882

MySQL Select - Order By using a Preferred Value

I have the following Select Statement:

SELECT user,color FROM accounts LIMIT 100;

The 'color' value has around 150 different values.

Is it possible to return all values but preference one.

So say - yellow is preference - could I get yellow at the top and then the rest returned after in one Query?

thanks

Upvotes: 1

Views: 339

Answers (4)

J261
J261

Reputation: 672

SELECT color, IF(color='yellow',1,2) AS top FROM colors ORDER BY top ASC

Upvotes: 2

James Joyce Alano
James Joyce Alano

Reputation: 703

(SELECT user,color
FROM accounts
WHERE color LIKE '%yellow%'
LIMIT 100)
UNION
(SELECT user,color
FROM accounts
WHERE color NOT LIKE '%yellow%'
LIMIT 100)

Upvotes: 1

Thanos Markou
Thanos Markou

Reputation: 2624

I think you need a sorting id.

Something like that:

SELECT user, color, WHEN color = 'YELLOW' then 1 else 2 end as SortingId FROM accounts LIMIT 100 ORDER BY SortingId;

Upvotes: 1

Marcel
Marcel

Reputation: 49

I would think the easiest is something like:

ORDER BY count(color);

This way always the most popular color should be on top.

Upvotes: -1

Related Questions