Is there a System Row Number Variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is there a System Row Number Variable?

Author

Topic

qwerks82
Starting Member

5 Posts

Posted - 2006-11-09 : 18:01:25

For MS SQL, so that I can query something like....

select * from myTable where #system_row_number = 5;

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 18:24:09

No, there are various things you can use depending on what you need, but none are exactly what you describe.
You can use an identity column - will have gaps in the numbers as rows are inserted and deleted.
You can use a rowversion column which holds an 8 byte value that you can cast as a number, it is unique in the database, but is not sequential for the rows in the table or contiguous because it changes every time a row is updated.

qwerks82
Starting Member

5 Posts

Posted - 2006-11-09 : 19:10:02

I can't create a table so I was thinking about doing a complex query where the inner query would grab the data and assign row numbers and then the outer query would grab the 2ND row so for example...

TABLE
------------------------------------
ID NAME xID
1 A 345
2 B 122
3 C 122
4 D 124
5 E 122

The inner query would grab the rows with xID of '122' would do result in something like this

TABLE
----------------------------
ROWNUM NAME
1 B
2 C
3 E

Then the outer query can grab the second one easily by just doing
select name from ....

So I guess my question is, how to create a query and insert a rownum

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-09 : 19:58:02

where do you want to show data?
If you dont use sql server 2005, then do numbering in front end application

Madhivanan

Failing to plan is Planning to fail

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 21:40:41

quote:
So I guess my question is, how to create a query and insert a rownum

You'd have to create a temporary table with an identity column.

qwerks82
Starting Member

5 Posts

Posted - 2006-11-10 : 09:53:33

I did something like...

CREATE TABLE #RowNumberComment (
RowNumber int IDENTITY (1, 1),
id int )

INSERT #RowNumberComment (id)
SELECT id
FROM tblPropertyComments
WHERE propertyid = 26
ORDER BY id

SELECT RowNumber, t.comment
FROM #RowNumberComment r JOIN tblPropertyComments t
ON r.id = t.id
ORDER BY RowNumber

DROP TABLE #RowNumberComment

but for some reason, the front end application complains about databind problems. So I was thinking doing a complex query with an inner and outer select statement. Is that possible? If so, can someone show me how? Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-10 : 13:32:45

That looks fine - the front end is probably creating a cursor or something else that depends on the underlying tables until it is done with the records, so don't put in that DROP TABLE statement. Temporary tables are automatically droppped when you close the connection, so it shouldn't be necessary anyway. If this won't work in the front end, a complex nested query isn't going to fix that.

qwerks82
Starting Member

5 Posts

Posted - 2006-11-10 : 14:57:44

select ROW_NUMBER() OVER (ORDER BY propertyid) as "rownum", @TempComment = comment from tblPropertyComments where propertyid = @PID and rownum = @CommentLoopCount

I get this error...
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

So I'm trying to do something like this where the inner query retrieves what I want with the row number, and then using that query selecting the row I want, but this isn't working, its probably a syntax problem.

select comment from
(select ROW_NUMBER() OVER (ORDER BY propertyid) as "rownum", comment
from tblPropertyComments where propertyid = 26)
where rownum = 2

I don't know what to google, what I want I thought was called a inner/outer complex query, but that's not what they call it. And I don't think its a union, join, intersect query.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-10 : 17:40:18

Have you tried this?

select ROW_NUMBER() OVER (ORDER BY propertyid) as "rownum", comment 
from tblPropertyComments 
where propertyid = @PID and rownum = @CommentLoopCount

qwerks82
Starting Member

5 Posts

Posted - 2006-11-10 : 17:42:05

I would but I need to somehow assign a variable to hold the comment.

@TempComment = comment

So that I can use that variable to do other things.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-13 : 13:38:47

Then

select @TempComment = t.comment
from
(select ROW_NUMBER() OVER (ORDER BY propertyid) as "rownum", comment 
from tblPropertyComments 
where propertyid = @PID and rownum = @CommentLoopCount) t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources