Thursday, January 11, 2007 3:33 PM
I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.
Select Count(*) As Counter
Group By Id
How can I number each individual row so that the row will have a number next to it i.e.
Select Count(*) As Counter,[Count Statement] as Number of the Row
Group By Id
Thursday, January 11, 2007 3:45 PMModerator
Is this what you want?
SELECTRow_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount
LEFTJOIN (SELECT COUNT(*) as myCount, id
groupby id) b ON a.id=b.id
Thursday, January 11, 2007 3:54 PM
I am not sure, I get the following error.
Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.
Thursday, January 11, 2007 4:16 PMModerator
Row_Number() is a new function in SQL Server 2005.
Try this one:
SELECT(select count(*) from items as t2 where t2.items <= t1.items )+1 as series_No, t1.id
Thursday, January 11, 2007 4:25 PMThanks, that helped.
Thursday, January 11, 2007 10:46 PMYou should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.