I think, answer 1:
select ctry_name, max(city_name) -- or min(city_name)
from country A, city B
where A.ctry_id = B.ctry_id
group by crty_name
order by ctry_name, city_name
Michel Cadot wrote:
> <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 Sat Nov 12 2005 - 02:38:39 CST
