How to use order by with specific value

Question

CREATE TABLE Countries(location varchar(255), country varchar(255))

INSERT INTO Countries(location, country)
               VALUES('Arkansas', 'US'),
                      ('Newyork', 'US'),
                      ('New Jersey', 'US'),
                      ('Tokyo', 'JP'),
                      ('Yokohama', 'JP'),
                      ('Chennai', 'IN'),
                      ('Delhi', 'IN'),
                      ('Sydney', 'AU'),
                      ('Melbourne', 'AU');

I need a query for the following output

 Location     |         Country
--------------------------------
  Arkansas                US
  Tokyo                   JP
  Chennai                 IN
  Sydney                  AU
  Newyork                 US
  Yokohama                JP
  Delhi                   IN
  Melbourne               AU
  New Jersey              US 

Answer 1

You need to give each location a rank based on its relative order within its own country. You can use variable to create a makeshift rownumber function in MySQL:

SELECT  Country,
        Location,
        @r:= CASE WHEN Country = @c THEN @r + 1 ELSE 1 END AS RN,
        @c:= Country AS C2
FROM    Countries,
        (SELECT @r:= 1) r,
        (SELECT @c:= '') c
ORDER BY Country, Location;

This will output

COUNTRY     LOCATION    RN  C2
AU          Melbourne   1   AU
AU          Sydney      2   AU
IN          Chennai     1   IN
IN          Delhi       2   IN
JP          Tokyo       1   JP
JP          Yokohama    2   JP
US          Arkansas    1   US
US          New Jersey  2   US
US          Newyork     3   US

Then you can order this by RN, and Country to get the order you want

SELECT  Location, Country
FROM    (   SELECT  Country,
                    Location,
                    @r:= CASE WHEN Country = @c THEN @r + 1 ELSE 1 END AS RN,
                    @c:= Country AS C2
            FROM    Countries,
                    (SELECT @r:= 1) r,
                    (SELECT @c:= '') c
            ORDER BY Country, Location
        ) c
ORDER BY rn, Country DESC;

Example on SQL Fiddle

EDIT

Since you are getting collation errors, but haven't specified what the collation errors are the only way I can hope to correct this is use explicit collation for everything:

SELECT  Location, Country
FROM    (   SELECT  Country COLLATE utf8_general_ci AS Country,
                    Location COLLATE utf8_general_ci AS Location,
                    @r:= CASE WHEN Country = @c THEN @r + 1 ELSE 1 END AS RN,
                    @c:= Country COLLATE utf8_general_ci AS C2
            FROM    Countries,
                    (SELECT @r:= 1) r,
                    (SELECT @c:= '' COLLATE utf8_general_ci) c
            ORDER BY Country, Location
        ) c
ORDER BY rn, Country DESC

SQL FIDDLE

Answer 2

You can't order your table the way you want without having an id. You could create your table this way:

CREATE TABLE Countries(
  id INT NOT NULL AUTO_INCREMENT,
  location varchar(255),
  country varchar(255),
  PRIMARY KEY(ID))

ant then you can insert your data:

INSERT INTO Countries(location, country)
           VALUES('Arkansas', 'US'),
           ...

Ant then I would write the query using just standard SQL as this:

SELECT *
FROM Countries c1
ORDER BY (select count(*)
          from countries c2
          where c1.country=c2.country
          and c1.id>c2.id), id

this query might not be fast, but it will work. But without using an id there's no way to answer your question. SQL tables have no default order, so if there's no id there's no way to tell that, for example, Sydney comes before Melbourne, even if it was inserted first.