SQL Experts

[LostInThought]

Any here?

I have a question...

[Mace]

I wouldn't say I'm an expert. But I know something about it. I'm sure others here do as well.

Why don't you post the question and we'll see?

Regards,

Mace.

[Suliman]

Lost in Thought: Dude you never let anyone see what you are doing.

Just ask the question and post a link to the site you are making, just go for it!

[LostInThought]

ok here goes.

if, on one of my tables i do

select * from mytable

it is still processing after a minute and if i stop the process it says over 1,000,000 rows returned...

what i want is for it to return 10 rows at a time. i know i can say top 10, i.e.

select top 10 * from mytable

but i also want to tell it where to start from.

in mysql there is this amazing function "limit" which lets you do exactly that, but it does not exist in sql server 2000. i know the row_number exists in sql server 2005, but i don't have that.

so in mysql i would do:

select * from mytable limit 0, 10

that is just beautiful! i.e. starting from the first row give me 10 rows.

anyway, how can i do that on sql server 2000 without creating a long winded stored procedure? get 10 rows based on a starting row. i am trying to page through the results 10 rows at a time per page without having to process ALL the rows on the sql server or the web server.

[ammarcool]

cant do it in SQL Server 2000, in SQL Server 2005 its possible.

[LostInThought]

cant do it in SQL Server 2000, in SQL Server 2005 its possible.

Looks like I'll have to use a stored procedure then...

[myself]

Have you tried putting in a condition?

select * from mytable WHERE name = *****

Just a thought. :o

[Mace]

Looks like I'll have to use a stored procedure then...

Or you could do a nested TOP in a subquery, and see how efficiently SQLServer will do that for you.

Something like select TOP 10 ... where ... not in ... select TOP 100 ...

and so on. But I have no idea how well SQLServer would optimize that.

Or if you have some sort of sequential key in that table on which you are sorting, you could just keep adjusting the range with that as a parameter. Depends on what your data looks like of course.

Sorry I don't have a more direct answer. I use mysql and Oracle myself. :)

[LostInThought]

Or you could do a nested TOP in a subquery, and see how efficiently SQLServer will do that for you.

Something like select TOP 10 ... where ... not in ... select TOP 100 ...

and so on. But I have no idea how well SQLServer would optimize that.

Or if you have some sort of sequential key in that table on which you are sorting, you could just keep adjusting the range with that as a parameter. Depends on what your data looks like of course.

Sorry I don't have a more direct answer. I use mysql and Oracle myself. :)

yes i used to use the "where ... not in" but that is only good for a small amount of rows. if the rows are in the millions then "ouch"...

basically, i need to page 10 rows at a time, but it seems impossible with SqlServer2k without a SP.

[ammarcool]

Looks like I'll have to use a stored procedure then...

yes brother. :)

just refer this:

SQL Server 2000 stored procedure to return paged results, paging SQL data. (LIMIT, TOP, MSSQL) (http://www.vwd-cms.com/Forum/Forums.aspx?topic=10)

[LostInThought]

yes brother. :)

just refer this:

SQL Server 2000 stored procedure to return paged results, paging SQL data. (LIMIT, TOP, MSSQL) (http://www.vwd-cms.com/Forum/Forums.aspx?topic=10)



I can't use that method, because it goes through all records and adds a numbered column. That is going to take ages to process and will be very very slow. I was looking for something that is instant.

[LostInThought]

It seems that this is the best that can be done with SqlServer2k

declare @RowPerPage int
declare @CurrentPage int

set @RowPerPage = 10
set @CurrentPage = 1

select top @RowPerPage the_id, the_column
from
(
select top @CurrentPage * @RowPerPage the_id, the_column
from the_table AS t1
order by @CurrentPage desc
) as t1
order by @CurrentPage asc