CODEWITHSUNDEEP
sqlms-accessvba
Faheem Ahmad
Faheem Ahmad

Reputation: 99

SQL Statement to work on a Microsoft Access Database which will get Top 5 Records but Skip the first 5

enter image description hereI need help with an SQL Statement to work on a Micrsoft Access Database which has a table named Tasks. I would like to get Top 5 Records but skiping the first 5 records. I am therefore looking for an SQL equivalent of the .Skip() method in LINQ which will work on an Micrsoft Access Database. I will need to put this query in a string variable

Dim myQuery As String
myQuery  = "Select *     "

Upvotes: 2

Views: 89

Answers (3)

dbmitch
dbmitch

Reputation: 5386

Changing the order won't work as you'd just get the top 5 again

I think you can get the next top 5 by doing a NOT IN first Top 5 as in

SELECT TOP 5 *
FROM Tasks
WHERE (ID Not In 
    (SELECT TOP 5 ID FROM Tasks ORDER BY ID)
ORDER BY ID;

In VBA that would just look the same with line continuation ("_") and concatenation ("&")

myQuery  = "SELECT TOP 5 * FROM Tasks " & _
    "WHERE (ID Not In (SELECT TOP 5 ID FROM Tasks ORDER BY ID) " & _
    "ORDER BY ID"

EDITED - Use a two step query and a temp table to speed up results 100 fold

  1. Make tmp table to store results of Top 5

SELECT TOP 5 ID INTO tmpTop5 FROM Tasks ORDER BY ID;

  1. Exclude those results from second Top 5 query

SELECT TOP 5 * FROM Tasks WHERE ID Not In (SELECT ID FROM tmpTop5) ORDER BY ID;

Upvotes: 0

Andomar
Andomar

Reputation: 238096

You can select the top 10 in a subquery, and select the bottom 5 of that in an outer query. Since you'd like the result sorted by ID asc, you can add another outer query for sorting:

select  *
from    (
        select  top (5) *
        from    (
                select  top (10) *
                from    YourTable
                order by
                        ID
                ) sub
        order by
                ID desc
        ) sub2
order by
        ID

Upvotes: 4

Gustav
Gustav

Reputation: 55816

I believe you just have to "reverse" the sorting in Andomar's SQL:

strSelectBooks = "Select Top 5 * From (Select Top 10 * From Tasks Where Person = 'Manager' Order By ID Desc) As Tasks10 Order By ID Asc"

Upvotes: 0

Related Questions