return row number in dataSet - SQL Server Forums

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

Transact-SQL (2000)

return row number in dataSet

Author

Topic

Ned
Starting Member

16 Posts

Posted - 2004-03-05 : 14:17:24

Hi,
I'm trying to return the row number for a query but I don't know the syntax. I'm sure it is easy I just don't know how.

For example, this doesn't work


Declare 
@item int
set @item = 0

select 
	@item = @item + 1
	,*
From
	Table

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 14:30:16

Hi Ned,

Why do you need the row number?

Ned
Starting Member

16 Posts

Posted - 2004-03-05 : 14:48:51

For display purposes...

I know the row number should probably be handled in the presentation layer but this dataset is bound to a ASP.NET datagrid so it would be easier to just return the dataset with an actual column for the row number.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 14:52:38

Here is one method:

SELECT identity(int,1,1) row_num,* 
INTO #table
FROM <table>

SELECT * 
FROM #table

DROP TABLE #table

Ned
Starting Member

16 Posts

Posted - 2004-03-05 : 15:00:11

Sorry I guess I wasn't clear. I don't want the actual row number, I want the rows to be numbered. For example if 10 rows were returned, I want the dataSet to be ordered 1 through 10.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-05 : 15:03:26

You might be able to do a Columns.Add on the dataset in ASP.Net to give you this same sort of thing as well. That way, you are handling presentation stuff in the presentation tier.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-05 : 15:35:21

quote:
SELECT identity(int,1,1) row_num,*
INTO #table
FROM <table>

SELECT *
FROM #table

DROP TABLE #table

quote:
Originally posted by Ned

Sorry I guess I wasn't clear. I don't want the actual row number, I want the rows to be numbered. For example if 10 rows were returned, I want the dataSet to be ordered 1 through 10.

ehorn just called the column "row_num" he wasn't bringing back the row number.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 15:56:04

quote:
Originally posted by MichaelP

You might be able to do a Columns.Add on the dataset in ASP.Net to give you this same sort of thing as well.

I believe the only way to handle this in the presentation layer using a bound datagrid would be to create a custom handler event and tie it to the DataGrid.OnItemDataBound Method using a label.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-05 : 23:26:46

Well, you can generate row numbers in SQL, but ehorn's suggestion is really the best way. It's easier and gives you a lot more flexibility with your SQL commands. Plus it's very easy to set up as a template that you can drop into any datagrid.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources