SQL Server Performance Forum – Threads Archive

Select Statement

I want a row index in the result of a query, ie, a field that will have 1 for the first row, 2 for the second row etc. Is it possible?

Assuming that your "row index" is stored in a standard SQL Server column, then this is easy, just include the row name in your SELECT statement. Generally, this is accomplished with an indentity column added to the table. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

Thank you very much for your fast response. But, the answer was not quite what I was looking for. 🙁 I want to get the index when it is NOT stored in the database. Also, I want the rowindex to always start from 1 and go sequentially upwards, whatever WHERE clauses are added to it, and whatever ordering is specified. In my research I found that I can create a temporary table and insert the records, and add an identity column in the temp table. I wanted to be sure that there is no other way to do this.

Do you have a primary key or a field with a unique contraint on the table? You could try something like this: SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id) AS rownumber
FROM employee e
ORDER BY emp_id Check out this link for a more complete description:
http://www.sqlteam.com/item.asp?ItemID=1491

Thanks "negative" for your "positive" contribution. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> —————— Brad M. McGehee Webmaster SQL-Server-Performance.Com

I’m sorry, but I was looking for the solution to a similar problem, and I seem to remember having seen the Identity used as part of a select (as opposed to part of a table definition). Anyone ever seen that before? Thanks, Francois

Whew, I knew id seen something like this before on MSDN. Took some tracking down, but here we go… http://support.microsoft.com/default.aspx?scid=KB;en-us;q186133 Hope this gives you some ideas.

]]>