Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!z14g2000cwz.googlegroups.com!not-for-mail
From: akchittanuri@gmail.com
Newsgroups: comp.databases.oracle.misc
Subject: Re: HELP with SQL
Date: 11 Nov 2005 02:51:01 -0800
Organization: http://groups.google.com
Lines: 44
Message-ID: <1131706261.589261.200600@z14g2000cwz.googlegroups.com>
References: <1131694734.595212.302740@g47g2000cwa.googlegroups.com>
   <43744fa4$0$16298$626a14ce@news.free.fr>
NNTP-Posting-Host: 203.199.183.30
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1131706269 27188 127.0.0.1 (11 Nov 2005 10:51:09 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 11 Nov 2005 10:51:09 +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: z14g2000cwz.googlegroups.com; posting-host=203.199.183.30;
   posting-account=DI5MIA0AAAA9trHlUiJFuNPjiF9NxofD
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:123255

Michael,
1) execution of the query

select a.ctry_name, b.city_name,
             row_number() over (partition by a.ctry_name, b.city_name)
rn
      from country a, city b where a.ctry_id = b.ctry_id

gave the error...

ORA-30485: missing ORDER BY expression in the window specification


2) execution of the query

select a.ctry_name, b.city_name,
             row_number() over (order by a.ctry_name, b.city_name) rn
      from country a, city b where a.ctry_id = b.ctry_id

gave the output

CTRY_NAME                      CITY_NAME
RN
------------------------------ ------------------------------
----------
Canada                         Ottawa
1
Canada                         Toronto
2
India                          Mumbai
3
USA                            New York
4
USA                            Phoenix
5
USA                            Washington
6
6 rows selected

IN the query(1), as i missing anything????

Thanks,
Anand.