have tree, get 5 records at a time? 1

[johnfraser]

I have a ASP.net Tree and due to our end users I've implimented load on demand using AJAX. They are looking for additional savings by limiting the amount of nodes returned to 5 (as an example).

So:

Parent:
-Child01
-Child02
-Child03
-Child04
-Child05
-More..

When I click on more, it would give me the next 5:


Parent:
-Child01
-Child02
-Child03
-Child04
-Child05
-Child06
-Child07
-Child08
-Child09
-Child10
-More..

I'm not a SQL guy, mostly a C# guy. I know how to get the top 10 results but not an easy way to get X number of results at a time.

I assumed I would provide a proc that would take in the number I want and the "seed number" Y which tells me to get X results starting at Y

 

[gmmastros]

What version of SQL Server are you using? This functionality was changed between SQL Server 2000 and SQL Server 2005.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnfraser]

I'm using SQL 2005. I'm gitty with excitement that we might be able to do this.

Thanks!

 

[gmmastros]

[blue]I know how to get the top 10 results but not an easy way to get X number of results at a time. [/blue]

Declare @Number Int
Set @Number = 5

Select Top (@Number) *
From   Table
Order By ColumnName

Does this help?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

>>Does this help?

No because it will always return the same 5 rows :-(

to get the next 5 rows you have to pass in what page number you are on

here is an example

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[johnfraser]

humm maybe I didn't propose my problem correctly.

using the example code you provided above will get me the top X number of results.

now lets say there is 20 total results.

I'd like to be able to do something like:

GetResults(int seed, int number)

Example:
GetResults(0,5)---Gets results 0-4
GetResults(5,5)---Gets results 5-9
GetResults(10,5)---Gets results 10-14
GetResults(15,5)---Gets results 15-19
GetResults(20,5)---Gets results 20

Basically an extension of "TOP(X)" that allows me to not only get the first X results but also X results starting at Y

 

[gmmastros]

How about this...

Declare @RowCount int

Set @StartNumber = 200
Set @RowCount = 10

Select Columns...
From   (
       Select [!]ColumnName[/!], Row_Number() Over (Order By [!]OrderByColumnName[/!]) As RowNumber
       From   [!]TableName[/!]
       ) As A
Where  RowNumber Between @StartNumber and @StartNumber + @RowCount -1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnfraser]

SQL Denis, exactly what I needed. Now to figure out exactly how to bind the "new query" to the existing data in the application side.