Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!news.tele.dk!news.tele.dk!small.news.tele.dk!proxad.net!infeed-2.proxad.net!news13-e.free.fr!not-for-mail
From: "Michel Cadot" <micadot{at}altern{dot}org>
Newsgroups: comp.databases.oracle.misc
References: <1131694734.595212.302740@g47g2000cwa.googlegroups.com>
Subject: Re: HELP with SQL
Date: Fri, 11 Nov 2005 09:00:36 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
Lines: 91
Message-ID: <43744fa4$0$16298$626a14ce@news.free.fr>
Organization: Guest of ProXad - France
NNTP-Posting-Date: 11 Nov 2005 09:00:36 MET
NNTP-Posting-Host: 82.67.171.166
X-Trace: 1131696036 news13-e.free.fr 16298 82.67.171.166:1991
X-Complaints-To: abuse@proxad.net
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:123254


<akchittanuri@gmail.com> a écrit dans le message de news: 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
|

First, see my answer to Wally in c.d.o.server.
Second, see my answer to Wally in c.d.o.server.

Sorry Daniel (Morgan), they got me on this one.

Regards
Michel Cadot