Re: SQL/SQL*Plus: How to add numbers to rows?

From: Gerard H. Pille <ghp_at_santens.be>
Date: 1997/10/17
Message-ID: <01bcdaf5$69b8e960$7b1340c0@pcghp.santens.be>#1/1

Not bad, it took me an hour.

select emp.affiliation,

       rownum - acnt.counter affseq,
       emp.name
from employees emp,
     ( select aff.affiliation, count(e.emp_no) counter
        from affiliations aff, employees e
        where aff.affiliation > e.affiliation(+)
        group by aff.affiliation) acnt

where emp.affiliation = acnt.affiliation order by 1

I wouldn't be to sure about the performance. Let me know if it works for your problem or if you have questions.

-- 
------------
Kind reGards
     \ /   |
      X    |
     / \   x
     Gerard

Steve Frampton <3srf_at_qlink.queensu.FOOBAR.ca> schreef in artikel
<6261al$b4q_at_knot.queensu.ca>...


> [From: header modified to thwart e-mail spam.  See .sig for details]

> 

> I've often wondered about how to accomplish something like this using

> SQL or SQL*Plus:

> 

> Affiliation   Employee Name

> -----------   ----------------------------

> AAW           1. Joe Smith

>               2. Henry Lee

> 

> Teamsters     1. Ralph Johnson

>               2. Norman Frank

>               3. Louis Short

>               4. Jane Doe

> 

> (I'm talking about the numeric entries).  I've tried a myriad of

> things including sequences (I get a "sequence not allowed here"

> error), rownum (I get stupid unordered rows because I am joining

> a number of tables and sorting data such as affilation codes and

> surnames).

> 

> How to display numeric lists?  I'm looking for an Oracle7 SQL or SQL*Plus

> solution.

> 

> Thanks in advance.

> 

> ----------------< LINUX: The choice of a GNU generation.

>----------------


> Steve Frampton   <3srf(@)qlink.queensu.ca>  

 http://qlink.queensu.ca/~3srf


> ----------- Please remove .FOOBAR from address before replying.

 ----------


> 

 

Received on Fri Oct 17 1997 - 00:00:00 CDT