How to generate a serial number in a request?

Question

We are using PostgreSQL v8.2.3.

How to create a serial number in the query output? I want to show the serial number for each row returned by the request.

Example: SELECT employeeid, name FROM employee

I expect to generate and display a serial number for each line, starting with one.

Answer 1

You have two options.

Go to the PostgreSQL v8.4 page and use the row_number() function:

 SELECT row_number() over (ORDER BY something) as num_by_something, * FROM table ORDER BY something; 

Or jump over some hoops as described in Modeling Line Number in PostgreSQL Pre 8.4 .

Answer 2

  SELECT row_number () over (order by employeeid) as serial_number,
        employeeid
        name
 FROM employee

If you want to assign numbers according to the sorting of the name, change the order in the over section