CODEWITHSUNDEEP
mysqlsql
user1187
user1187

Reputation: 2218

How to use order by with specific value

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

Upvotes: 6

Views: 258

Answers (6)

fthiella
fthiella

Reputation: 49079

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.

Upvotes: 1

ArrayOutOfBound
ArrayOutOfBound

Reputation: 2638

enter image description here

why this happens.

its showing MySQL Database Error: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Upvotes: 0

ArrayOutOfBound
ArrayOutOfBound

Reputation: 2638

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;

There should be no Order by in subquery

Upvotes: 0

GarethD
GarethD

Reputation: 69789

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

Upvotes: 4

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

you can perform this query with oracle Analytical function rank()

here is the working query 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;

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24076

try this:

Use order by Field() in mysql

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

Upvotes: 1

Related Questions