ID column or Row Count Column 1

[tqeonline]

I need to add a row count column on a query and I guess i'm bad at googling.

Say i have 10 rows returned I'd like a column that beside row 1 shows a 1, row 2 a 2... etc.

Any ideas?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[ousoonerjoe]

SELECT [RowId] = CAST(ROW_NUMBER() OVER(ORDER BY FieldName) AS SMALLMONEY)
FROM TableName
WHERE ....;

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[tqeonline]

That does it!

Thanks!

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[ousoonerjoe]

You should read up more on ROW_NUMBER(). In combination with GROUP BY you can create numbered lists by GROUP.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------