(Postgress) actual record number in recordset

Question

Ffor testing purposes I need to write a SQL query which contains the actual record number as a column in the result set. If my SELECT gets back to me with 10 records as the result, I need to have one column which contains the values 1-10.

Is there a way to achieve this without a stored procedure cursoring through my data?

EDIT: I need this on postgresql.

Answer 1

Have a look at ROW_NUMBER() (SQL Server 2005 and above)

Answer 2

You could partition your data and get a row_number()

For example:

SELECT FirstName, LastName, SalesYTD, PostalCode, 
       ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

See the following: ROW_NUMBER (Transact-SQL)

Answer 3

For postgresql see: Simulating Row Number in PostgreSQL Pre 8.4

Answer 4

If you're on 8.4, you can use window functions (row_number() to be exact).

If you're on pre 8.4, you can use the technique I described some time ago on my blog.