Number the rows in a record set

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)

Number the rows in a record set

Author

Topic

JimB2
Starting Member

2 Posts

Posted - 2006-02-03 : 14:33:39

I want to number the rows in a record set as it is created. This is an ordered set and I would use the row number in a treeview display.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-02-03 : 14:44:15

Welcome to SQLTeam,

would advice, Lets see your table structure

You can use the auto increment integer value. Is this what you want ?

JimB2
Starting Member

2 Posts

Posted - 2006-02-03 : 17:01:32

I want to create a record set to use in a treeview on a web page, the data is based on some SQL view. I want to search for an employee name with partial information. Perhaps the first name is guessed from one or two choices - Bill or William and the last name starts with T or Th. Or I want to find an office by name that I don't remember fully. Look ups could be done; then go to a tree structure to see the surrounding office/personnel structure of the selected person or office. Someone created a temp table for the record set and added an integer field and called it counter1, then did an update to that column. See the code:
DECLARE @counter int
SET @counter = 0
UPDATE #tmp_Users
SET @counter = counter = @counter + 1

This may work.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-03 : 18:15:32

create the temp table with int identity and insert your result set into that temp table and then select back from the temp table

create table #temp
(
    row_number int identity(1,1),
    col1 ...
    col2 ...
)

insert into #temp(col1, col2, ...)
    select ....
    from   ....
    where  ....
    order by ...

select * from #temp order by row_number

----------------------------------
'KH'

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-02-03 : 18:44:29

You could use @@RowCount and put that into an output parameter in your stored proc.

Do a search in the Books Online for @@rowcount.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources