[sclug] Numbering records in a postgres query result set

[Paul Vanlint]

The easiest way I know of doing something like this is to use

create temporary sequence a;
select nextval('a'), * from tblorganisation where organisationid > 50
limit 5;
drop sequence a;

Paul.

On Fri, 2003-05-02 at 12:37, Tim Sutton wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hi
> 
> I wonder if anyone can help me with this. I have a query that returns a bunch 
> of records:
> 
> sp2kmetadb=# select * from tblorganisation where organisationid > 50 limit 5;
>  organisationid |            name
> - ----------------+-----------------------------
>              51 | University of Nottingham
>              52 | C.G. Sibley
>              53 | Forschungszentrum Karlsruhe
>              54 | Frei Universitat Berlin
>              55 | University of Geneva
> 
> I need an addition column included in the query output which is a incremental 
> count e.g.:
> 
> 
> number| organisationid |            name
> - -------+----------------+-----------------------------
>   1   |        51 | University of Nottingham
>   2   |        52 | C.G. Sibley
>   3   |        53 | Forschungszentrum Karlsruhe
>   4   |        54 | Frei Universitat Berlin
>   5   |        55 | University of Geneva
> 
> Is there a quick and dirty way to do this?
> 
> Thanks
> 
> - -- 
> Tim Sutton
> BDWorld Middleware Programmer
> - -------------------------------------------------------------------
> BiodiversityWorld Project
> Centre for Plant Diversity & Systematics
> School of Plant Sciences
> The University of Reading
> Reading, RG66AS, UK
> 
> Web     :   www.bdworld.org
> Phone  :   +44-(0)118-378-6052  
> Email    :   t.sutton [at] reading.ac.uk
> (email preferred method of correspondence)
> - -------------------------------------------------------------------
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
> 
> iD8DBQE+slhhZxb+GYjbYHkRArT3AJ9U3DJfMMvaMtnQ528iGb/k4OcagACfdxpC
> pCcKxu67SjuatEnyoFLzPFM=
> =qML7
> -----END PGP SIGNATURE-----
> 
> _______________________________________________
> sclug mailing list
> sclug at sclug.org.uk
> http://www.sclug.org.uk/mailman/listinfo/sclug
>