asp.net consultancy chorley ASP.NET Server Side Scripting Wigan
Website Development Radcliffe

IT Services And Support

Email : ren@techsolus.co.uk
Mobile Phone : 0788 68 41 411
Answerphone : 01204 469683
bespoke invoice systems Standish Information Technology Advice Worsley
    development systems Ramsbottom
data manipulation Farnworth access databases Whitefield SQL connections Atherton
online accessible software Leyland software compatible Coppull


Get connected and Online Tottington
remote management Makerfield code and design Aspull

Access SQL LIMIT Alternative - 18/02/2016

Grrrrr!
 
MS Access is a fine and dandy database for those of us who work with smaller databases. It's easy to backup an "*.mdb" file, it's simple to create queries using clickety click wizards and unless your data runs into the millions upon millions of entries spread across a corporate network Access will usually suffice.
 
But there are some niggles.
 
Other variants of SQL have the command "LIMIT" which makes pagination nice and simple. Pagination? For example you have a web page and you want to display forum posts. Selecting the "TOP 20" entries is simple but selecting the entries from 10 to 20 is much more complex.
 
In other versions you'd use
 
SELECT * FROM table LIMIT 10, 10
or
SELECT * FROM table LIMIT 10 OFFSET 10
 
In MS Access there is no such thing as "LIMIT" though. Here's my code to achieve the same...
 
SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 * FROM mytable ORDER BY myid DESC) ORDER BY myid ASC) ORDER BY myid DESC
 
WHAT?!?!
 
OK...so follow the brackets. Our first instruction is (SELECT TOP 20 * FROM mytable ORDER BY myid DESC). We select the top 20 entries with this statement. Now we have entries 1 to 20 in this "SELECT" statement. Around this is (SELECT TOP 10 * FROM ....... ORDER BY myid ASC). Here by using the "ORDER BY" statement in reverse from our original top 20 we can select the bottom 10 for want of a better phrase. We are selecting entries 11 to 20 here. Around all this is SELECT * FROM ..... ORDER BY myid DESC. This is simply to allow us to order out final results in the way we want them. For example 11, 12, 13, 14... or 20, 19, 18, 17...
 
It's hard work but effective. Play with the "ORDER BY" to get the results you desire and "TOP X" to get the values you require. 
 
 

Post A Comment

Name Comment
programming services asp.net specialist
Valid XHTML 1.0 Transitional
Admin
GD