3

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?

I need this on PostgreSQL.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
KB22
  • 6,899
  • 9
  • 43
  • 52

3 Answers3

8

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)

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Jason Irwin
  • 1,985
  • 2
  • 29
  • 42
  • The good thing about this solution is it will work for any RDBMS that is written to the ISO SQL:2003 standard. – J. Polfer Sep 09 '09 at 14:34
5

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

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
2

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.