How do I get the row number?

[LordOfCode]

Hello everybody.

I need to build a query whose one of its fields is the row number of the result set.

In oracle I can get the row number, but I do not know hoy I can do that in SQL Server.

Can somebody help me, please?

edalorzo@lidersoft.com

 

[Durkin]

Insert your data into a table variable but have an identity column in the table variable.

Durkin

 

[JamesLean]

Check out this FAQ:

How Do I Get a Row Number On Each Row of a Query Result Set?
faq183-3021

--James

 

[LordOfCode]

I already knew I could do a temporary table, but We're building a highly transactional application and we would like to control the number of rows a user asks to the server by means of paging information in the client application.

The application we're building must be independent of the database arquitecture, so we cannot compromise with the use of SQL Server temporary table or stored procedures.

I just thought there was a statement, like in Oracle, by means of which I could do that.

Although I cannot use your tips, thank you very much to both of you.

edalorzo@lidersoft.com

 

[JayKusch]

You may use the command "set rowcount = ". So if we set rowcount = 100, in theory, only a set of 100 records is returned even if the "fully" returned set has more than 100 records.

Thanks

J. Kusch

 

[LordOfCode]

Row Count just tells you how many fields have been affected by a query. It does not mean you just can retrieve that amount of records.

Thanks anyway!

edalorzo@hotmail.com

 

[cheyney]

straight from BOL:


SET ROWCOUNT
Causes Microsoft® SQL Server™ to stop processing the query after the specified number of rows are returned.


Cheyney

 

[royjimenez]

I think that the users shoudn't have the oportunity to do a huge retrieves,
'cause if several users does similars selects at the same time it will be too bad.
U should get more information to make the query or if u have the problem only for one
query, u could get the count(*) to compare with a parameters in your application, and
in this way, u will know if u must retrieve the rows or not ( u could show a message
"Too many rows will be retrieve, u should specify more information in your query" ).


In SQL Server, Select Top 10 * from Table is similar to ROWNUM in Oracle, but
Set ROWCOUNT TO is better for u needs.

if u need more help, u can reach at roy_jimenez@costarricense.cr or royjimcam@hotmail.com

Regards

 

[JayKusch]

LordOfCode ...

you are talking about the system variable of @@rowcount.

Thanks

J. Kusch

 

[LordOfCode]

Oh, now I understand. I'm sorry. I thought you meant @@rowcount.

You're right I can get a set of records with set rowcount.

But still with this I have another problem.

This is what I would like to do:

In Oracle code

select * from 
   (
    select a.*, rownum as r from MyTable a
   )
where r between 10 and 20
[code]

However, even with the set rowcount, I cannot do this, because rowcount sets a limit on the result set, but what I need is to filter a range of records by means of its rownumber.

Any idea? Somebody?


edalorzo@hotmail.com

 

[LordOfCode]

I cannot set the rownumber once I have received the data in the client application, because the purpose is to reduce the network traffic and to improve memory management in the client application.

We pretend to emulate some source of paging, so that we can retrieve records on demand, and not all the records at once.

edalorzo@hotmail.com