Sort certain values to the top
mysql, sql-order-by
Solution
Try this:
SELECT * FROM stores ORDER BY country = "us" DESC, storeId
Problem
I have a MySQL table with the following data (simplified): ``` INSERT INTO `stores` (`storeId`, `name`, `country`) VALUES (1, 'Foo', 'us'), (2, 'Bar', 'jp'), (3, 'Baz', 'us'), (4, 'Foo2', 'se'), (5, 'Baz2', 'jp'), (6, 'Bar3', 'jp'); ``` Now, I want to be able to get a paginated list of stores that begins with the customers country. For example, an american customer would see the following list: ``` Foo Baz Bar Foo2 Baz2 Bar3 ``` The naive solution I'm using right now (example with an american customer and page size 3): ``` (SELECT * FROM stores WHERE country = "us") UNION (SELECT * FROM stores WHERE country != "us") LIMIT 0,3 ``` Are there any better ways of doing this? Could `ORDER BY` be used and told to put a certain value at the top?