Re: SQL question - rownum related

In article <36658E30.81AD8A31_at_2xtreme.net>,   PMG <pete_g_at_2xtreme.net> wrote:
> Hi there,
>
> I have an EMPLOYEES table with a CITY column. I need to generate a table
> containing a unique list of cities from EMPLOYEES and a new column which
> is a sequential number. I have been successful using the following
> query:
>
> INSERT INTO newtable (
> SELECT rownum, city
> FROM employees a
> WHERE a.rowid <= (
> SELECT MIN(b.rowid)
> FROM employees b
> WHERE a.city = b.city));
>
> This gives me a table such as
> 1 Los Angeles
> 2 San Francisco
> 3 Chicago
> etc.
>
> How do I modify this query to return the cities in sorted order. That
> is,
> 1 Chicago
> 2 Los Angeles
> 3 San Francisco
>
> TIA
>
> Pete
>
>

You could try something like
insert into newtable
select rownum,city
from (select distinct city from employees);

It will return rows sorted in proper order, because "distinct" uses sort algorithm to eliminate duplicates, and is somewhat simpler then your DML.

--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Dec 02 1998 - 20:05:50 CST