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
  • 159,224
  • 26
  • 237
  • 393
KB22
  • 6,699
  • 8
  • 41
  • 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
  • 159,224
  • 26
  • 237
  • 393
Jason Irwin
  • 1,995
  • 2
  • 28
  • 41
  • 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
  • 85,870
  • 40
  • 177
  • 264
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.