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

Please clarify how you want your results. The example output given is not in any order. You want exactly one location from each country, ignoring the other locations? How do you propose to pick which location should be returned? And shall we assume you want the results sorted by country? Or did you mean you want all the rows sorted by country, and within each country to sort them by location? — WarrenT, Nov 24 '12 at 23:09

score 4 · Answer 1 · edited Nov 25 '12 at 17:23

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

@r and @c are variables. On each row it is essentially saying if the country of this row is the same as the country on the previous row add 1 to the variable @r (This is how you get your row number for each location), then after this the variable @c is used to store the country from the previous row so the row number column can reset to 1 when the country changes. — GarethD, Nov 22 '12 at 09:20
I assume this should be collations not collections, does it tell you where the error is occurring and which collations are illegal? — GarethD, Nov 22 '12 at 09:25
Why is Arkansas ahead of New York, but Sydney ahead of Melbourne? — GarethD, Nov 22 '12 at 09:56
this is the error ---Category Timestamp Duration Message Line Position Error 11/22/2012 3:46:58 PM 0:00:00.015 - MySQL Database Error: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' 31 0 — user1187, Nov 22 '12 at 10:17
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/19932/discussion-between-athi-and-garethd) — user1187, Nov 22 '12 at 10:27

score 1 · Answer 2 · answered Nov 22 '12 at 09:04

1

try this:

Use order by Field() in mysql

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

answered Nov 22 '12 at 09:04

Joe G Joseph

23,518
5
56
58

what is Filed? wahat i want to give – user1187 Nov 22 '12 at 09:07
based on country i want to order – user1187 Nov 22 '12 at 09:09
iwant to take 1st US location 2nd JP location like that – user1187 Nov 22 '12 at 09:10

score 1 · Answer 3 · answered Nov 25 '12 at 15:59

1

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;

answered Nov 25 '12 at 15:59

Ajith Sasidharan

1,155
7
7

2

What are you implying by your answer? That the OP can use an Oracle analytical function in MySQL? Or that they should switch from MySQL to Oracle Database so that they can use analytical functions? Please be specific in your answer. – Andriy M Nov 25 '12 at 16:30

fthiella · Accepted Answer · 2012-11-26T20:13:23.637

1

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.

edited Nov 26 '12 at 20:13

answered Nov 26 '12 at 19:46

fthiella

48,073
15
90
106

http://stackoverflow.com/a/13518125/833073 here for example I explain why you can't have a query that returns the first value of something, if you don't have a field you can use in your ORDER BY clause – fthiella Nov 26 '12 at 20:22
see also this link http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order about default order – fthiella Nov 26 '12 at 20:22

score 0 · Answer 5 · answered Nov 25 '12 at 17:56

0

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

answered Nov 25 '12 at 17:56

ArrayOutOfBound

2,614
5
20
26

You need the order by in the subquery to ensure @r and @c are incremented correctly. It only works in your example because of the order the data is inserted. If the order is changed on the insert you get incorrect results without the order by [Example with changed insert order](http://www.sqlfiddle.com/#!2/dc496/2) – GarethD Nov 26 '12 at 08:46

score 0 · Answer 6 · answered Nov 26 '12 at 04:33

0

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 '='

answered Nov 26 '12 at 04:33

ArrayOutOfBound

2,614
5
20
26

How to use order by with specific value

6 Answers6