Numbering the query results

[SteveSQL]

I have a very basic question. I'd like to be able to have a column that holds the number (selected index) of each result just like the grid is numbered.

Example

select * from employees

col 1
1
2
3
4

how would I do this?

 

[gmmastros]

I recommend you do this on the front end. Meaning, before displaying the results in a grid, set up a counter variable. Then, when looping through the result set, add the number to the grid and increment the counter by one.

There are ways to do this in SQL, but in my opinion, is better handled in the front end app. If you still want the query to display the row number, let me know and I will help you out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DrSql]

Here is a logic that you can try..
[tt]
declare @num as int
declare @exists int
declare @au_id as char(15)
DROP TABLE #TBL_temp
declare cur_count cursor for

select au_id from authors
SELECT @NUM=0
SELECT @num AS NUM, au_id, au_lname, au_fname, phone, address, city, state, zip, contract into #TBL_temp from authors
DELETE #TBL_temp
open cur_count
readnext:
fetch next from cur_count into
@au_id


If @@FETCH_STATUS <> 0
goto eof
If @au_id<>''
Begin
select @num=@num+1
Print @num
Print @au_id
Insert into #TBL_temp
SELECT @num AS NUM, au_id, au_lname, au_fname, phone, address, city, state, zip, contract from authors where au_id=@au_id
end
goto readnext
eof:
close cur_count
deallocate cur_count

SELECT * FROM #TBL_temp
--DROP TABLE #TBL_temp

[/tt]

Dr.Sql
Good Luck.

 

[DrSql]

Run the above script on PUBS db

Dr.Sql
Good Luck.