data arranged in alphabetical order then paged in groups of 10

Question

data arranged in alphabetical order then paged in groups of 10

mick burden

SSCarpal Tunnel

Points: 4432
More actions
March 7, 2011 at 6:35 am

#246576

is there a way of getting data back in alphabetical order in groups of say ten, I need this for for a webpage, is this possible?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you posted in SQL2000; are you limited to using sql 2000 for this solution?

sql 2005 introduced the row_number() function, which makes it really easy to do; your page would just need to pas the integer representing which group/page of data.

my example below is grabbing groups of 25; this command is getting the third group of the possible values.

--SQL 2005 + solution

declare @WhichPage int

Set @WhichPage = 3

SELECT * FROM (

select row_number() OVER(ORDER BY NAME) AS RWNBR, * FROM SYSOBJECTS) MYALIAS

WHERE RWNBR BETWEEN (25 * (@WhichPage -1)) AND (25 * (@WhichPage -1)) + 25

---returns rows 75 thru 100 inclusive (26 rows)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 2

thanks for the quick reply, at the present I need this for SQL2000. The senario I want to use this in a webpage where you click on a letter, say "B" and I'd get a return of the first 10 records of anything starting with B, then when I click on "Next" I get the next 10 and so on, so I really need to be able to pass in a letter for the seach and the page number. I will be upgrading to SQL2008 in the future.

Lowell SSC Guru Points: 323518 More actions · Answer 3

mick i'm afraid i'll end up sending you to do some reading; i don't have a handy SQL 2000 solution to paste here for you;

as i remember it, if your data isn't being sorted on an ID identity of the table, you have to use a temp table to get the results, or a set of nested TOP X SELECT statements.

this is one article i have bookmarked for "paging sql 2000":

http://www.4guysfromrolla.com/webtech/042606-1.shtml

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mick burden SSCarpal Tunnel Points: 4432 More actions · Answer 4

Thanks Lowell for your help on this, I've now got a couple of ideas to try