CODEWITHSUNDEEP
mysql
GSto
GSto

Reputation: 42350

MySQL, sort alphabetically with exception

I have a table that is a list of countries, with two fields, ID (int) and country_name. I need to write a query that can pull an alphabetical list of countries, but with the United States record at the top. How can I do this? assume that the id of the united states record = 100.

Upvotes: 1

Views: 1185

Answers (4)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40021

select * 
  from country
 order by case when id = 100 then 0 else 1 end, country_name

Extra note.

This solution differs from the other suggested in some ways

  • The UNION way. The default behavior of UNION is to remove duplicates, this may or may not be desired (Unions in MySQL). Also unless id is indexed you will have twice the execution time
  • The "Select something and do sorting on it" is nice, but returns extra data and depending on the receiving framework this might not be desired.

Upvotes: 6

flesk
flesk

Reputation: 7579

select (case when id = 100 then 1 else 0) presort, country_name from countries order by presort desc, country_name asc

Selects a flag aliased presort, which is 1 for id = 100 and 0 for everything else. The selected rows are then sorted descending which puts id = 100 first, then by country_name ascending when presort is 0, which is the case for all other ids.

Upvotes: 1

Danny
Danny

Reputation: 7518

Do two separate queries using union.

select id, country_name from table where id=100
union
select id, country_name from table where id!=100 order by country_name desc;

I haven't tested this, but in theory it should work.

Upvotes: 0

danielrsmith
danielrsmith

Reputation: 4060

(SELECT *
FROM countries
WHERE id = 100)
UNION
(SELECT *
FROM countries
WHERE id != 100
ORDER BY country_name)

Not necessarily the best way to do it. I'd prefer to just hard code the USA at the top when using it, in the model layer of sorts.

Upvotes: 0

Related Questions