Sorting certain values to the top

Question

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?

Answer 1

You have to link each value of a country with a numeric, with a case :

select *
from stores
order by case when country = "us" then 1
              else 0
         end desc

Answer 2

Create a table of country codes and orders, join to it in your query, and then order by the country code's order.

So you would have a table that looks like

CountryOrder

Code  Ord
----  ---
us    1
jp    2
se    3

and then code that looks like:

SELECT s.*
FROM Stores s
INNER JOIN CountryOrder c
   ON c.Code = s.Country
ORDER BY c.Ord;

Answer 3

How about using 'IF' to assign the top value to US rows.

select if(country_cd='us,'aaaUS',country_cd) sort_country_cd, country_cd from stores Order by sort_country_cd

This will give you a pseudo column called "sort_country_cd" . Here you can map "US" to "aaaUS". "JP" can still be mapped to "JP".

That puts US on the top of your sort list.

Answer 4

Try this:

SELECT * FROM stores ORDER BY country = "us" DESC,  storeId

Answer 5

To get the searched-for country first, and the remainder alphabetically:

SELECT * 
FROM   stores 
ORDER BY country = 'us' DESC, country ASC