| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL/SQL*Plus: How to add numbers to rows?
Sorry, but your query will not work. Even assuming that SELECT in FROM clause returns what you expected (which is not the case). Oracle assigns ROWNUM before applying ORDER BY clause, therefore you can not rely on ROWNUM being returned in sequence. Now about SELECT in FROM clause. For every affiliation in sorted sequence it returns total number of employees in all subsequent affiliations. For the last one it returns 0. If you use Steve's example, SELECT in FROM clause will return:
AFFILIATION COUNTER
--------------------- ------------------ AAW 4 Teamsters 0
Even assuming that employee table is already sorted by affiliation your query would return:
Affiliation Employee Name
----------- ----------------------------
AAW -3. Joe Smith
-2. Henry Lee
Teamsters 3. Ralph Johnson
4. Norman Frank
5 . Louis Short
6. Jane Doe
You should change where aff.affiliation > e.affiliation(+) to where
aff.affiliation < e.affiliation(+) then SELECT in FROM clause would
return total number of employees in all preceding affiliations. For the
first one it would return 0.
AFFILIATION COUNTER
--------------------- ------------------ AAW 0 Teamsters 2
and assuming that employee table is already sorted by affiliation (which is not necessary the case) query would return right results.
Check query in my reply to Steve.
Solomon Yakobson.
In article <01bcdaf5$69b8e960$7b1340c0_at_pcghp.santens.be>,
"Gerard H. Pille" <ghp_at_santens.be> wrote:
>
> 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.
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Fri Oct 17 1997 - 00:00:00 CDT
 |
 |