Re: Help: Numbering rows returned by a SQL query

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Tue, 27 Oct 1998 08:19:32 -0800
Message-ID: <3635F294.69406509@uclink4.berkeley.edu>

Ted,

If the table has a unique column, say emp_id, you could group the table by an in-line view and do the following:

1 SELECT rownum, surname
2 FROM ( SELECT surname

  3             FROM emp
  4*        GROUP BY surname, emp_id)

SQL> / ROWNUM SURNAME

--------- -------------------------------
        1 Adams
        2 Brown
        3 Thomas
        4 Young

...if you don't have a unique column (or a set of unique columns) then you will drop duplicate records with the GROUP BY

Jay!!!

Edward Tate wrote:

> Does anyone know how I can number the rows returned
> by a SQL statement, for example
>
> SELECT SURNAME
> FROM EMP
> ORDER BY SURNAME
> /
>
> to produce
>
> 1 ADAMS
> 2 BROWN
> 3 THOMAS
> 4 YOUNG
>
> I have tried using rownum, but of course the numbers are jumbled
> by the alpha sort on the surname!
>
> Any suggestions would be most welcome.
>
> Thanks in advance.
>
> Ted Tate
> University of Wales College, Newport
>
> e.j.tate_at_NOSPAM.newport.ac.uk
>
> >>> Remove the NOSPAM. in the address when replying. <<<
Received on Tue Oct 27 1998 - 10:19:32 CST