Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 1998-09-15
11
Medium Priority
?
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 4

Expert Comment

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

Author Comment

by:webcast
ID: 1090054
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
ID: 1090055
Create autoincrement primary key and use :where id between 1 and 100, where id between 101 and 200 and so on.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:webcast
ID: 1090056
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
ID: 1090057
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
 

Author Comment

by:webcast
ID: 1090058
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
ID: 1090059
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
ID: 1090060
how can i delete this quesiton? or at least close it?
0
 

Expert Comment

by:scarlett
ID: 1090061
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
ID: 1090062
You could have done it by using a cursor in the sp
0
 
LVL 1

Accepted Solution

by:
pure032398 earned 80 total points
ID: 1090063
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

704 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