Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP with SQL

Re: HELP with SQL

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 11 Nov 2005 09:00:36 +0100
Message-ID: <43744fa4$0$16298$626a14ce@news.free.fr>

<akchittanuri_at_gmail.com> a écrit dans le message de news: 1131694734.595212.302740_at_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 Received on Fri Nov 11 2005 - 02:00:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US