Q
Problem solve Get help with specific problems with your technologies, process and projects.

Adding a count to a recordset

I have a question that seems simple, but I cannot find anything about this online, so here we go.

I have a query that returns a list of IDs from a table (no problem), but I also need to add a count to the recordset so that each record is numbered sequentially. For example, the recordset should look like:

 ID Count ----------------- 28 1 34 2 49 3 55 4 59 5
and so on... this is probably easy, but when I search SQL help or MSDN or anywhere, I of course get info on counting the entire recordset. Basically I just need to add a sequential ID to the list of IDs. Oh, and I would prefer to do this without a tempTable (already thought of that one...).

You are right. It is a simple question however there is not a simple answer. The best option is probably the temp table solution that you have thought of, e.g.

 Create table #MyTempTable(id int, count int identity(1,1)) Insert #MyTempTable(id) Select id From MyTable order by ID

 

For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close