Local Query Row Number

[BigDaddy]

Good morning. I need some help. I need to pass two fields into (eventually) a combo box. In order to handle the user selection, I need to have a description field (strReferredDiv) and an index (RowNum). I have a class that can accept a sql statement and loop through and marshal the data to my UI front end classes which load into the combo box.

The problem I am having is for the situation that I don't have a field for my index field to use. (This is within a criteria reporting form) So I would like to formulate a Transact SQL statement that gives me my fields. RowNum (increments a counter for each row), description (strReferredDiv, in my sample). Any Ideas?

My sample
doesn't increment the @intVar for each record.
/********************/
declare @intVar int

select @intVar = 0
select (@intvar + 1) RowNum, strReferredDiv
from tblActionRequest
group by strReferredDiv
Order by RowNum
/********************/
RETURNS
RowNum strReferredDiv
----------- ----------------------------------------
1 Information Services
1 Solid Waste
1 Street Maintenance
1 Transportation Administration

When I would like it to return:

RowNum strReferredDiv
----------- ----------------------------------------
1 Information Services
2 Solid Waste
3 Street Maintenance
4 Transportation Administration

 

[tlbroadbent]

You can create a temporary table with an identity column. This is best done in a stored procedure.

Create procedure sp_myproc as

Create table #tmp (RowNum int identity (1,1), strReferredDiv varchar(40))

Insert Into #tmp
select strReferredDiv
from tblActionRequest
group by strReferredDiv

Select * From #tmp
Order by RowNum

Drop table #tmp Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[BigDaddy]

Thanks for the help Terry. I was trying to handle this by passing only SQL to the class that goes to the database to retrieve the items for my combobox. I will investigate this idea of a stored procedure and get back to you.

The reason for my reluctance to use stored procedures is that my class is a TextList class that handles passing tables or SQL Statements to it and retrieves an ADODB.Recordset. I believe (and I will investigate) that I would need to define a different set of objects to use the ADODB.Command Object.

Great help though. I really appreciate the fast response.