Re: Probably simple answer

You could use a procedure to select the data into a table which has a sequence as a default for the number column, then
selectyour data out of that.  The function could return a key if the same table might be used by more than one user, in
whichcase the sequence would have to be set back to value '1' for each use, or you could create a temp table each time. 

It seems like psql could be hacked to do something like this.  Alternatively, whichever client you are using could also
behacked.  It seems simpler to add the numbering as the data is presented to the user than to do it in the database,
especiallysince the numbers have no shelf life.  They are meaningless after any of the underlying data has been deleted
orappended. 

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.org

>>> Andrew Gould <andrewgould@yahoo.com> 11/06/01 07:41AM >>>
I think he simply wants the rows of the result set
numbered from 1 to whatever the last row is.  This is
useful for referencing rows when discussing reports.
It is also an immense help when calculating a record's
statistical percentile ranking.

Doing it in a table is no problem.  But how do you
create one dynamically in a select query?

I too would like to know if and how this could be
done.

Any ideas?

Andrew Gould


--- postgresql <pgsql@symcom.com> wrote:
> I think you need to define more clearly what you
> want as an end
> product. You could create a serial column however
> there are
> possibilities of skipped numbers. Next, how do these
> 'row number'
> relate to your data when you delete a row? Do you
> expect that the
> remaining rows will re-number themselves? Is this
> 'number' a client
> id number?
>
> This sounds like you need to better define your data
> needs.
> Before anyone can help, you need to be clearer.
>
> Ted
>
> -----Original Message-----
> From: "Al Kirkus" <Al@dist102.k12.il.us>
> To: pgsql-general@postgresql.org
> Date: Thu, 01 Nov 2001 14:24:29 -0600
> Subject: [GENERAL] Probably simple answer
>
> > Hi all.
> >
> > Can anyone tell me how to get a sequential row
> count field in the
> > output of a query?
> >
> > Say I want to query for all users in a table
> sorted by lastname and
> > firstname.
> > I would like to include a column in my query
> called "rownum" which
> > would uniquely identify the row in the order of
> the query results.
> >
> > Like this:
> >
> > rownum =1   lastname=jones, firstname=john
> > rownum=2 lastname=smith, firstname=john
> >
> > etc.
> > I assume rownum should be some kind of function of
> expresion
> but I
> > don't know what.
> >
> > Something like:
> >
> > Select ???? as rownum, lastname,firstname from
> users where xxx
> =xxx
> > order by lastname, firsname.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> send an
> appropriate
> > subscribe-nomail command to
> majordomo@postgresql.org so
> that your
> > message can get through to the mailing list
> cleanly
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org