By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,794 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

Query limits

P: 20
Hi again,

I have an access query like this:

"SELECT Sheet1.* FROM Sheet1;"

The result is about 1,000 or 1,500 records.

How can I limit the result, showing only from record number 50 to record number 100.

Is it possible?

BTW, I have no ID field (just in case)
Feb 17 '10 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 2.5K+
P: 2,545
Hi Cynicon, and Welcome to Bytes!

There is no single SQL operator which can do what you want. The nearest is SELECT TOP:

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 100 yourfield1, yourfield2 etc FROM yourtable ... etc
This will return the top 100 records in whatever table or query you wish to reduce the volume on, but you cannot exclude the first 50 that way. It may be possible to do it by using right-joining between a SELECT TOP 100 query and a SELECT TOP 50 query, returning only the rows where keys don't match, but I reckon this would be a very slow approach (as you would be running the complete dataset twice simply to get rid of the first N rows from the result set).

There may be other ways to do this using subqueries, but without seeing your data it is difficult to give you more guidance. If you could post some example data we could review it and see if there is a way to help you that does not involve considerable run-time processing.

You mention that you don't have an ID field - not sure why not, as it makes joining the results much more difficult if there is no unique ID available.

Feb 17 '10 #2

P: 579
Will it mess up your data too much to add an ID field?

I would think that you could loop through the recordset until you get to record number 50 and stop when you get to record number 100, but I'd have to play with it to get it to filter according to only those values.

Like Stewart mentioned, without knowing what kind of data is stored in the tables, it's almost impossible to give you a solution that will work in all instances.
Feb 17 '10 #3

Expert Mod 15k+
P: 31,492
I don't think you'd expect a facility such as this in something like SQL. However, Stewart has shown how to do it. Simply join two subqueries (See Subqueries in SQL for help with that) with the TOP predicates set to 50 & 100 and include only those that don't match on a unique ID. If you don't have one of those (or any way of unique identification) then you need to think about adding one. There's a good reason for them. We don't just create them for fun. They're fundamental to most database work.
Feb 19 '10 #4

P: 20
Sorry about the delay in my reply all of you have been so kind while giving answers, Ill check all of your answers and let you know as asap the results, thank you
Feb 23 '10 #5

Expert Mod 15k+
P: 31,492
That's perfectly reasonable. As it's a web-based forum, we don't expect immediate responses :)
Feb 24 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.