sql resultset records numbering ?

Question

I want to number the rows in my result set. I want some way I can have a result set of 3 records use some SQL keyword to generate a column that would read 1,2,3 for watch of the records...

I know I can make a temp table with an auto increment column but i wanted to know if there was a way I can get this back from a SQL query?

SELECT row_count, 
       project_name, 
       project_id 
  FROM Project 

anything available like "row_count" that i am dreaming of?

Answer 1

Sounds like you want ROW_NUMBER, an analytic/ranking function. But it's unclear what you want to base the numbering on - this assumes the project_id, starting at the smallest value:

SELECT ROW_NUMBER() OVER(ORDER BY p.project_id) AS row_count,
       p.project_name,
       p.project_id
  FROM PROJECT p

Answer 2

Ordering by SELECT 0 will give you an incrementing column and saves a potentially unnecessary sort.

SELECT ROW_NUMBER() over (order by (select 0)) as row_count, 
       project_name, 
       project_id 
  FROM Project