Getting a row count for each record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting a row count for each record

Author

Topic

jrp210
Starting Member

7 Posts

Posted - 2003-11-24 : 15:55:48

How can I include the row count for each record in my recordset? I.E. I have a query like this:

SELECT CustomerID, CompanyName
FROM Customers

And now want to add another field that contains the record count for each row:

SELECT CustomerID, CompanyName, RowCount
FROM Customers

My output would look like this:

ALFKI Alfreds Futterkiste 1
ANATR Ana Trujillo Emparedados y helados 2
ANTON Antonio Moreno Taquería 3
AROUT Around the Horn 4
BERGS Berglunds snabbköp 5

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-24 : 16:06:48

select .... rowcnt = (select count(*) from Customers c2 where c2.CustomerID <= c1.CustomerID)
from Customers c1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jrp210
Starting Member

7 Posts

Posted - 2003-11-24 : 16:49:24

thanks!

jrp210
Starting Member

7 Posts

Posted - 2003-11-24 : 16:58:11

Oops, spoke too soon. I used your code and then applied a filter on my sql statement. The rowcnt that is being returned is the actual row count in the table. If I have 20 records returned, I need it to begin with number 1, 2, 3, 4 ... all the way to 20.

Granick
Starting Member

46 Posts

Posted - 2003-11-24 : 17:02:53

You should be able to apply the same criteria to the Subquery that you added to the WHERE clause on the base SELECT and it should still give you what you are looking for.

Example:
select .... rowcnt = (select count(*) from Customers c2 where c2.CustomerID <= c1.CustomerID WHERE CompanyName <> 'Test%')
from Customers c1
WHERE
CompanyName <> 'Test%'

Does that make sense?

Shannon

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-24 : 17:47:47

Easiest probably to put the data into a temp table with an identity and return that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nathan_d_s
Starting Member

4 Posts

Posted - 2003-11-25 : 04:49:11

Try like this

SELECT IDENTITY(int,1,1) AS RowNo, CustomerID, CompanyName INTO #Temp1 FROM Customers
SELECT * From #Temp1
DROP TABLE #Temp1

tap_ks
Starting Member

2 Posts

Posted - 2003-11-25 : 08:21:59

Thanks nr.

I was looking for the query for along time.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 08:42:56

And, as always:

Why do you need this? for further calcuation/sorting/filtering? if so, definitely do it in SQL. But if it is just to display on a report or a web page, do it at the presentation layer. Much more efficient and easy to do.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources