CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Anu
Anu

Reputation: 21

Ordering Query for Country & City data for the scenario given

My input data is below :

**Country          city**
Australia     Sydney
Australia     melbourne
India         Delhi
India         Chennai
India         Bangalore
Afghanistan   Kabul

Output expected is:

Afghanistan
Kabul
Australia
melbourne
syndey
India
Bangalore
Chennai
Delhi

The data in both columns should be arranged alphabetically(both city level and country level) and result should be single column with above values. The country should be alphabetically ordered and the corresponding cities should go below them which should also be alphabetically ordered. How can this be done without using an intermediate table in a single query?

Upvotes: 1

Views: 375

Answers (2)

MT0
MT0

Reputation: 168061

This has a single table scan and also does not need to use UNION to get distinct results:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE cities ( Country, city ) AS
SELECT 'Australia',     'Sydney' FROM DUAL UNION ALL
SELECT 'Australia',     'melbourne' FROM DUAL UNION ALL
SELECT 'India',         'Delhi' FROM DUAL UNION ALL
SELECT 'India',         'Chennai' FROM DUAL UNION ALL
SELECT 'India',         'Bangalore' FROM DUAL UNION ALL
SELECT 'Afghanistan',   'Kabul' FROM DUAL;

Query 1:

SELECT value
FROM   (
  SELECT c.*,
         country AS ctry,
         ROW_NUMBER() OVER ( PARTITION BY Country ORDER BY city ) AS rn
  FROM   cities c
)
UNPIVOT( value FOR key IN ( Country AS 1, City AS 2 ) )
WHERE rn = 1 OR key = 2
ORDER BY ctry, rn, key

Results:

|       VALUE |
|-------------|
| Afghanistan |
|       Kabul |
|   Australia |
|      Sydney |
|   melbourne |
|       India |
|   Bangalore |
|     Chennai |
|       Delhi |

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94959

You need a UNION ALL query to get one row per country and one row per city in your result:

select coalesce(city, country) as location
from
(
  select distinct country, null as city from mytable
  union all
  select country, city from mytable
)
order by country, city nulls first;

Upvotes: 2

Related Questions