Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 1998-09-15
11
Medium Priority
?
479 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:Victor 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

972 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