How to generate serial number in a query?

We're using PostgreSQL v8.2.3.

How do I generate serial number in the query output? I want to display serial number for each row returned by the query.

Example: SELECT employeeid, name FROM employee

I expect to generate and display serial number against开发者_如何学JAVA each row starting from one.

You have two options.

Either upgrade to PostgreSQL v8.4 and use the row_number() function:

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

Or jump through some hoops as described in Simulating Row Number in PostgreSQL Pre 8.4.

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

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