Solved

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

Posted on 1998-09-15
11
450 Views
Last Modified: 2010-03-19
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...
0
Comment
Question by:webcast
11 Comments
 
LVL 4

Expert Comment

by:vvk
Comment Utility
You need  SET ROWCOUNT command to specify count of returned in result set rows. This command has session scope.
0
 

Author Comment

by:webcast
Comment Utility
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.
0
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
Create autoincrement primary key and use :where id between 1 and 100, where id between 101 and 200 and so on.
0
 

Author Comment

by:webcast
Comment Utility
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...

0
 

Expert Comment

by:doubas
Comment Utility
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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:webcast
Comment Utility
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...
0
 

Author Comment

by:webcast
Comment Utility
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
0
 

Author Comment

by:webcast
Comment Utility
how can i delete this quesiton? or at least close it?
0
 

Expert Comment

by:scarlett
Comment Utility
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.
0
 
LVL 1

Expert Comment

by:JeroenW
Comment Utility
You could have done it by using a cursor in the sp
0
 
LVL 1

Accepted Solution

by:
pure032398 earned 20 total points
Comment Utility
Check out http:<u're web server>/iishelp/ado/docs/adoprox1.htm
for an example of this. (using absolute page, pagecount and pagesize..)

also, look under the ado docs for info on recordsets. this explains the properties of this, suchas as pagesize, which limits the number of records to be displayed per page...

good luck!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now