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

This reply is a bit late but I just re-subscribed and trying to catch up. As another poster noted, this could be done easily with PL/SQL. But since the requirement is to produce it through plain SQL, the query done by Gerard H. Pille below is a very good start, except that it won't return the last column in a sorted order.

I started with that query and continued to produce the required result. If performance is not an issue (small databases) then this will do:

--
Using different data for testing puposes, the tables are:

SQL> SELECT * FROM dept;        SQL> SELECT * FROM emp;

         ID NAME                    DEPT_ID LAST_NAME
----------- ----------------    ----------- ----------------
         20 RESEARCH                     30 JAMES
         10 ACCOUNTING                   10 CLARK
         40 OPERATIONS                   30 MARTIN
         30 SALES                        30 TURNER
                                         30 WARD
4 rows selected.                         10 MILLER
                                         20 SMITH
                                         20 ADAM
                                         30 ALLEN

                                9 rows selected.

[Notice that natural order in either table is unsorted]

Now trying Gerard's method:

SQL> BREAK ON dept_id SKIP 1
SQL> SELECT emp.dept_id,
  2         rownum - deptcnt.counter deptseq,
  3         emp.last_name
  4  FROM emp,
  5       (SELECT dept.id,
  6               COUNT(e.last_name) counter
  7        FROM dept, emp e
  8        WHERE dept.id > e.dept_id (+)
  9        GROUP BY dept.id) deptcnt
 10  WHERE emp.dept_id = deptcnt.id
 11  ORDER BY 1
 12  /

DEPT_ID     DEPTSEQ     LAST_NAME
----------- ----------- --------------------
         10           1 CLARK
                      2 MILLER

         20           1 SMITH
                      2 ADAM

         30           1 JAMES
                      2 ALLEN
                      3 MARTIN
                      4 TURNER
                      5 WARD


9 rows selected.
[LAST_NAME Column is NOT sorted!]

Here's my method, Notice that I used Gerard's Sub-Select for my bb
clause below (Change the hard-coded 15 below for whatever Maximum
Length your "Dept ID" field will have):

SQL> BREAK ON "Department" SKIP 1
SQL> SELECT rownum,
            TO_NUMBER(aa."Dept") "Department",
            aa."RowNum" - bb."Count" "Counter",
            aa."LName" "Last Name"
     FROM


       (SELECT RTRIM(SUBSTR(a."temp",1,15)) "Dept",

               SUBSTR(a."temp",15) "LName",
               COUNT(*) "RowNum"
        FROM
          (SELECT DISTINCT 
                  RPAD(TO_CHAR(dept_id),15) || last_name "temp"
           FROM emp) a,
          (SELECT DISTINCT 
                  RPAD(TO_CHAR(dept_id),15) || last_name "temp"
           FROM emp) b
        WHERE a."temp" >= b."temp"
        GROUP BY RTRIM(SUBSTR(a."temp",1,15)),
                 SUBSTR(a."temp",15)) aa,


       (SELECT dept.id "Dept",

               COUNT(emp.last_name) "Count"
        FROM dept, emp
        WHERE dept.id > emp.dept_id (+)
        GROUP BY dept.id) bb
     WHERE aa."Dept" = bb."Dept"
     /

ROWNUM  Department     Counter Last Name
----------- ----------- ----------- ---------------------
          1          10           1  CLARK
          2                       2  MILLER

          3          20           1  ADAM
          4                       2  SMITH

          5          30           1  ALLEN
          6                       2  JAMES
          7                       3  MARTIN
          8                       4  TURNER
          9                       5  WARD

9 rows selected.
[All Columns are ordered!]

Here's one with outer join:

SQL> SELECT rownum,
            TO_NUMBER(aa."Dept") "Department",
            aa."RowNum" - bb."Count" "Counter",
            aa."LName" "Last Name"
     FROM


       (SELECT RTRIM(SUBSTR(a."temp",1,15)) "Dept",

               SUBSTR(a."temp",15) "LName",
               COUNT(*) "RowNum"
        FROM
          (SELECT DISTINCT 
                  RPAD(TO_CHAR(dept.id),15) || emp.last_name "temp"
           FROM dept,emp
           WHERE dept.id = emp.dept_id (+)) a,
          (SELECT DISTINCT
                  RPAD(TO_CHAR(dept.id),15) || emp.last_name "temp"
           FROM dept,emp
           WHERE dept.id = emp.dept_id (+)) b
        WHERE a."temp" >= b."temp"
        GROUP BY RTRIM(SUBSTR(a."temp",1,15)),
                 SUBSTR(a."temp",15)) aa,


       (SELECT dept.id "Dept",

               COUNT(emp.last_name) "Count"
        FROM dept, emp
        WHERE dept.id > emp.dept_id (+)
        GROUP BY dept.id) bb
     WHERE aa."Dept" = bb."Dept"
     /

ROWNUM  Department     Counter Last Name
----------- ----------- ----------- ---------------------
          1          10           1  CLARK
          2                       2  MILLER

          3          20           1  ADAM
          4                       2  SMITH

          5          30           1  ALLEN
          6                       2  JAMES
          7                       3  MARTIN
          8                       4  TURNER
          9                       5  WARD

         10          40           0

10 rows selected.


Best regards
Amine Tarhini
Tarhinia_at_hotmail.com


syakobson_at_erols.com wrote in article <877135123.8695_at_dejanews.com>...
> 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

... [ Lines deleted for brevity]

> 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
>