Link to home
Start Free TrialLog in
Avatar of webcast
webcast

asked on

LIMIT ? - How to limit number of returned records..

Hi All,

   How do i limit the number of returned records.

Eg. Imagine I wish to get the first record->tenth records returned only.

And later on I want the thirty-first record-> forty'th records returned...

etc.

I saw some SQL like this ..

".... WHERE artist like 'a%' order by artist limit 0,100 "

See that "limit 0,100...". I thought this means sow 0->100 records only..

But i get an error when i try to do that in my SQL..

Any suggesstions?

Sorry about the meagre points - this is all i have left...
Avatar of vvk
vvk

You need  SET ROWCOUNT command to specify count of returned in result set rows. This command has session scope.
Avatar of webcast

ASKER

Nop. That sets the number of rows to display (max number).

i want rows x->y from the database.

eg.

i want to get rows 5->10 (if there are, say 30 rows in the db).

You can say (OH!) make the code (ASP) only display it .. yeah true, but this defeats the purpose of a quick(er) SQL retrieval.

So .. any other suggesstions folks?

J.
Create autoincrement primary key and use :where id between 1 and 100, where id between 101 and 200 and so on.
Avatar of webcast

ASKER

Nop - the primary keys are (basically) incrememnted, but they are not nessissarily in perfect order ...

By this i mean. There might be 1, 2, 3, 4 and 5. But number 6 and 7 might be deleted. 8 is there 9 is now gone. 10 is there, etc.. so this COULD return all rows between 1 and 100, but not 100 rows.

Thanks anyways for the suggesstion - anyone else please...

does your table/query meets the following criteria?

a) you have a single column which contains unique values (possibly has a unique index created on it)

b) when considering records m-n, you are talking about records m-n for this column in sorted order.

for your particular example: assuming the column artist is unique, and you want artist m-n (in alphabetical order by artist), the query below should do it for you.  note that your results don't necessarily have to be sorted by artist, you just have to want artists m-n in sorted order.

for example, if the available artists are prince, abba, queen, megadeth and slayer, and you want to retrieve artists 2-4, then you mean megadeth, prince, and queen.

here's the query, using variables for readiblity...

declare @m        int,
        @n        int,
        @numRows  int

-- let's say you want rows 20 - 25
select @m = 20
select @n = 25
select @numRows = @n - @m + 1

-- limit output to 6 rows
set rowcount @numRows

-- get rows beginning with m
select a.artist
from artists a
where artist like "a%"
  and @m <= (select count(*)
              from artists b
             where b.artist like "a%"
               and b.artist <= a.artist)

i sincerely hope that helps.  if it doesn't meet your requirements, you might be able to modify it so that it does.  at any rate, good luck.

doug
Avatar of webcast

ASKER

I found the answer - it's NOT SQL related. I'm sorry for the inconvience - it's related to ASP - using ABSOLUTEPAGE, PAGECOUNT, etc..

Sorry - i though SQL could handle pages...
Avatar of webcast

ASKER

Oops. The page didn't refresh, so i didn't see the proposed answer.

Once more - sorry *blush*

I've done it in asp, so i'm not going to try that SQL. Looks interesting though .. i see what you have done.

Thank you very much for the help though :-)

*bows*

Jussy
Avatar of webcast

ASKER

how can i delete this quesiton? or at least close it?
Don't know if there's a better way but if you can get doubas to send you another pseudo-answer - using the answer form rather than the comment form then you can accept the answer and it will close out.
You could have done it by using a cursor in the sp
ASKER CERTIFIED SOLUTION
Avatar of pure032398
pure032398

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial