How to use an order for a 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 request 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 cannot order the table the way you want without id . You can create a 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 a query using only standard SQL, like this:

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

this request may not be fast, but it will work. But without using id there is no way to answer your question. SQL tables have no default order, so if there is no id , there is no way to say that, for example, Sydney comes to Melbourne, even if it was inserted first.

Answer 2

You need to give each place a rank in accordance with its relative order within its own country. You can use a variable to create a temporary 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 lead to the conclusion

 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 it by RN and country to get your desired order.

 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; 

SQL script example

EDIT

Since you get matching errors, but didn't indicate which matching errors are the only way, I can hope to fix this, use explicit matching 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 3

try the following:

Use field order () in mysql

 select Location, Country from Countries order by Field(Location,'Arkansas','Tokyo','Chennai',...) 

Answer 4

you can execute this query with oracle. Analytic function rank ()

here is a working request for the same

 select tbl.l, tbl.c from ( select location l, country c, rank() over (partition by country order by rowid) rnk from countries order by rowid,rnk) tbl order by rnk,rowid; 

Answer 5

  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
         ) c
  ORDER BY rn, Country DESC;

In subquery

there should be no order.

Answer 6

Why is this happening.

its display MySQL database error: invalid combination of sorts (latin1_general_ci, IMPLICIT) and (latin1_swedish_ci, IMPLICIT) for operation '='