Solved

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

Posted on 1998-09-15
11
455 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
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 20 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

860 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