Re: SQL question - rownum related

You are so right. Thanks for your version. It's amazing how often one finds a complicated route when a much simpler route is right in front of his nose.

Thanks again...

mgogala_at_rocketmail.com wrote:

> 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 - 23:41:01 CST