Solved

SQL only return range of records

Posted on 2008-10-23
4
528 Views
Last Modified: 2013-11-25
Is there sql to only return back a range of records i.e from 20-30 in access.  or are there any easy work arrounds to this problem

thanks

dave
0
Comment
Question by:david_88
4 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 22784452
Hi david_88,

Something like
SELECT * FROM Table1 WHERE ((Table1.ID) Between 20 And 30);

Good Luck!

Gary
0
 
LVL 6

Accepted Solution

by:
cr4ck3rj4ck earned 500 total points
ID: 22784477
In MySQL you can use the LIMIT function but in MSACCESS you have to use a sub query to create an offset.

select top X * from myTable where id not in (select top Y * from myTable)

X would be replaced by the amount of records you wanted to return.
Y would be replaced by the position within the data set at which you wanted to start.
id would be your unique identifier within the table.

For example:
select top 10 * from myTable where id not in (select top 19 * from myTable)

That would return records 20-30 in a recordset.
0
 
LVL 5

Expert Comment

by:eridanix
ID: 22784598
Hi,

this is example of SQL paging. You can simply modify it as you need:

top_onPage = "TOP " + Convert.ToString((onPage * page > totalCount) ? (totalCount % onPage) : (onPage));
top_page = "TOP " + Convert.ToString(onPage * page);

SELECT * FROM (
      SELECT TOP " + top_onPage + " id  FROM (
            SELECT DISTINCT " + top_page + " T.id
                  FROM dbo.tblTest T
            )      as A ORDER BY X ASC
)      as B ORDER BY X DESC
0
 

Author Closing Comment

by:david_88
ID: 31509138
perect  thanks!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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