HELP with SQL

From: <akchittanuri_at_gmail.com>
Date: 10 Nov 2005 23:38:54 -0800
Message-ID: <1131694734.595212.302740@g47g2000cwa.googlegroups.com>

Hello,

here are two tables in ORACLE

create table country

(

      ctry_id number(11),
      ctry_name varchar2(30)

    );

create table city

(

      city_id number(11),
      city_name varchar2(30),
      ctry_id number(11)

    );

insert into country values(1,'USA');
insert into country values(2,'Canada');
insert into country values(3,'India');

insert into city values(1,'New York',1);
insert into city values(2,'Washington',1);
insert into city values(3,'Phoenix',1);
insert into city values(4,'Toronto',2);

insert into city values(5,'Ottawa',2);
insert into city values(6,'Mumbai',3);

result of the qry

select ctry_name, city_name from country A, city B where A.ctry_id = B.ctry_id
order by ctry_name, city_name

is

CTRY_NAME       CITY_NAME
---------       ---------
Canada          Ottawa
Canada          Toronto
India           Mumbai
USA             New York
USA             Phoenix
USA             Washington

Q) First, I need to get a list of all countries and any one city from each of the countries.
for example, the output may be as follows:

CTRY_NAME       CITY_NAME
---------       ---------
Canada          Ottawa
India           Mumbai
USA             Phoenix

Q) Second, I need to rank the cities alphabetically within each country. My out put should be as follows:

CTRY_NAME       CITY_NAME      CITY_RANK
---------       ---------      ---------
Canada          Ottawa             1
Canada          Toronto            2
India           Mumbai             1
USA             New York           1
USA             Phoenix            2
USA             Washington         3

How to get the above output?? Can we do this by SQL queries or do we need PL./SQL??

Pls. solve these asap.

Thanks a ton in advance..
Anand Received on Fri Nov 11 2005 - 01:38:54 CST