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 -> SQL question - rownum related

SQL question - rownum related

From: PMG <pete_g_at_2xtreme.net>
Date: Wed, 02 Dec 1998 18:59:55 GMT
Message-ID: <36658E30.81AD8A31@2xtreme.net>


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

Original text of this message

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