How to create sql for retrieving 10 records per time in .NET

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.
ThanksAs a great man once said: Huh?

I'm sure that your posting made sense to you, but I don't even have a clue what you meant. Could you try to repost and fill in a few of the details that I'm missing? I'd like to help you if I could, but at the moment I don't understand enough of what you want to have a chance!

-PatP|||Hi,

as Pat said, this isn't very clear and you really should repost. I just want to comment on what you may need to consider mentioning.

Almost all databases are used by multiple users at the same time. Selecting the 10 first records in a particular order makes perfect sense, as does selecting record 11 to 20 of that same recordset. But selecting 10 and then (at a later time) selecting "the ten next" doesn't have any clear meaning since the data may well have changed in the meantime. So perhaps it's a good idea to clarify how you want your system to deal with the different change scenarios: records may have been added, removed or updated, or any mix of the three between the two queries.

One way to "page in the database":
Initialize:
- establish a single orderby column for your query (if you have several columns, combine them into one column yielding equivalent sort order)
- select the top 10 records
- persist the value of the orderby column for the last record. In ASP.NET, use ViewState for this.

Each round trip:
- use the value of the last record in the previous result set to select the next ten:
SELECT TOP 10 ... WHERE ... AND ordCol > @.lastValue ORDER BY ordCol
- persist the last value again

Of course, if you require to page the other way (previous page) you must also persist the value of the first record. Furthermore, since there's no BOTTOM keyword to mirror TOP, you must use reverse sort order (otherwise, if you did SELECT TOP 10 ... WHERE ordCol < @.firstVal, the first page would be returned even if you tried to get the previous one from, say, page 5). Since you still want to display in-order, fix this by selecting into a temp table and then select in-order from that one:

SELECT TOP 10 INTO #page ... WHERE ... AND ordCol < @.firstVal ORDER BY ordCol DESC
SELECT * FROM #page ORDER BY ordCol

I hope this helps!

Dag|||Or you could just set-up paging in a datagrid...|||Huh?

Just a guess...

You want to do paging

http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx

Make sure to say hi to Jeff for me
__________________|||I meant that you could use a .NET DataGrid control that allows you to view a recordset one page at a time. You can define how many records you want displayed on each page. Of course this deals with paging at the Application level - don't know if that's what he wants but just thought I'd mention it.

I don't have a clue about paging at the SQL Server level...:-)|||Sorry for unclear question. Yes, what i want to do is paging.
Brett: The URL http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx is unavailable
mmcdonald: Thanks for your suggestion but the user don't want the result be shown in datagrid :(