How to find the item index number in a query result

Question

Hi, is there a simple way to get the number of the current item in a simple SELECT? I need to deliver a column based on a calculation that involves the number of the current index in the select. I am simplifing my problem to an extreme, but roughly speaking, here is an example:

SELECT column1 * ITEMINDEX FROM table

I hope I am being clear. I am using SQL Server. Thank you.

Answer 1

In SQL Server 2005+:

SELECT  m.*, ROW_NUMBER() OVER (ORDER BY column) AS rn
FROM    mytable m

SQL does not have concept of implicit row number, that's why you need ORDER BY clause to define the order of rows.