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 5and 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
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.