SQL question - rownum related

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 Received on Wed Dec 02 1998 - 12:59:55 CST