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 -> Re: SQL/SQL*Plus: How to add numbers to rows?

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

From: <syakobson_at_erols.com>
Date: 1997/10/17
Message-ID: <877132117.6286@dejanews.com>#1/1

It is not too difficult to do:

SELECT DECODE(count(*),1,e1.affiliation,NULL) Affiliation,

               count(*)||'. '||e1.name Name   FROM emp e1,

             emp e2
  WHERE e1.affiliation = e2.affiliation

        AND e1.name >= e2.name
  GROUP BY e1.affiliation,

                      e1.name;

The query above joins employee table to itself by affiliation and for every employee calculates how many employees within the same affiliation have names less or equal (we need equal because the same employee exists in both tables) than that employee's name. Anotherwords, for every employee it calculates employee's sequential number within affiliation. Decode takes care of displaying affiliation value for each affiliation first employee only. The query above will not work properly if affiliation and name combination is not unique (e.g. you can have more than one Joe Smith within affiliation AAW). If this is a case, all you need is to group by affiliation, name and rowid, which will produce uniqueness:

SELECT DECODE(count(*), 1, e1.affiliation, NULL) Affiliation,

               count(*) || '. ' || e1.name Name   FROM emp e1,

             emp e2
  WHERE e1.affiliation = e2.affiliation

        AND e1.name || e1.rowid >= e2.name || e2.rowid   GROUP BY e1.affiliation,

                      e1.name,
                      e1.rowid;


Solomon Yakobson.

In article <6261al$b4q_at_knot.queensu.ca>,   Steve Frampton <3srf_at_qlink.queensu.FOOBAR.ca> wrote:
>
> [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

Original text of this message

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