Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g47g2000cwa.googlegroups.com!not-for-mail
From: akchittanuri@gmail.com
Newsgroups: comp.databases.oracle.misc
Subject: HELP with SQL
Date: 10 Nov 2005 23:38:54 -0800
Organization: http://groups.google.com
Lines: 79
Message-ID: <1131694734.595212.302740@g47g2000cwa.googlegroups.com>
NNTP-Posting-Host: 203.199.183.30
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1131694739 3643 127.0.0.1 (11 Nov 2005 07:38:59 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 11 Nov 2005 07:38:59 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: g47g2000cwa.googlegroups.com; posting-host=203.199.183.30;
   posting-account=DI5MIA0AAAA9trHlUiJFuNPjiF9NxofD
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:123253

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