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

I need help with an SQL Statement to work on a Micrsoft Access Database which has a table named Books. 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.

Thank you for your help.
LVL 1
FaheemAhmadGulAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
I need help with an SQL Statement to work on a Micrsoft Access Database

Then it doesn't make much sense to check our code in SSMS.
So, back in Access, this does provide the result requested:

SELECT Top 5 * 
FROM
    (SELECT Top 10 * 
    FROM Tasks 
    WHERE Person="Manager"
    ORDER BY ID DESC) AS Tasks10
ORDER BY ID ASC

Open in new window

EE-all.pngEE-Top-10-5.png
For the reverse order:

SELECT *
FROM
    (SELECT TOP 5 *
    FROM 
        (SELECT Top 10 *
        FROM Tasks
        WHERE Person="Manager"
        ORDER BY ID ASC)  AS Tasks10
    ORDER BY ID DESC) AS Tasks5
ORDER BY ID ASC

Open in new window

EE-10-5.PNG
0
 
Gustav BrockCIOCommented:
Try this:

Select Top 5 * 
From Books
Where ID Not In (Select Top 5 * From Books Order By ID Desc)

Open in new window

0
 
FaheemAhmadGulAuthor Commented:
Thank you for your comment.
I need to put this query in a string Variable and I am doing it like this:
strSelectBooks = "Select Top * 5  From Books Where ID Not In (Select Top 5  *  from Books Where Person='Manager')"

Open in new window


However, the above query string gives an error. Please let me know if I am doing it correctly.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Gustav BrockCIOCommented:
Hard to tell with neither the table nor the error. But I had left out the last order by so try:

strSelectBooks = "Select Top * 5  From Books Where ID Not In (Select Top 5  *  from Books As T Where T.Person='Manager' Order By T.ID Desc) Order By ID Desc"

Open in new window

0
 
FaheemAhmadGulAuthor Commented:
This also give an error. The error I get is
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Error Number: -2147217900
0
 
Gustav BrockCIOCommented:
That typically indicates a missing or misspelled field or table name.

With no further info, I can't tell more. Double-check.
0
 
FaheemAhmadGulAuthor Commented:
I have double checked. I am sure the Table and Field names are correct. Could it be that this works with SQL Server but not with Access?
0
 
FaheemAhmadGulAuthor Commented:
I have tried this query in an SQL Server and in that also it shows an error. Please note that in the SQL Server the table is Tasks instead of Books.
I include a screen shot of where SQL points the error is.Error Message Screen shot in SQL ServerError Message Screen shot in SQL Server
0
 
Gustav BrockCIOCommented:
Oh, sorry, it should read:

strSelectBooks = "Select Top 5 * From Tasks Where ID Not In (Select Top 5  *  from Tasks As T Where T.Person='Manager' Order By T.ID Desc) Order By ID Desc"

Open in new window

However, as you have seen on SO, a faster method could be to pick first Top 10, then Top 5 of these:

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

Open in new window

0
 
hnasrCommented:
Try this modification to Gustav's code

strSelectBooks = " Select * From (Select Top 5 * From (Select Top 10  *  from Tasks Where Person='Manager' Order By ID ) As Tasks10 Order By ID Desc) Order By ID"
0
 
John TsioumprisSoftware & Systems EngineerCommented:
You could also fetch top 10 records and skip while processing the first 5
0
 
FaheemAhmadGulAuthor Commented:
Thank you all experts for your comments. I have tried all the suggestions but none is giving the results - some give erros and the one that does get some results does not get the records I would like to fetch.

A screen shot of trial all three queries will explain what I mean. Three proposed queries and results
0
 
hnasrCommented:
The example presented in the screenshot is from SQL server.
Check this Statement, that works for table A and field a.
SELECT *
FROM (SELECT TOP 3 * 
FROM (SELECT TOP 6 * FROM A ORDER BY a DESC) as top10 )AS tbl ORDER BY a

Open in new window


Try this modification of the statement in the screenshot.
SELECT *
FROM (SELECT TOP 5 * 
FROM (SELECT TOP 10 * FROM Tasks WHERE Person='Manager' ORDER BY ID DESC) as top10 )AS tbl ORDER BY ID

Open in new window

0
 
FaheemAhmadGulAuthor Commented:
This works perfectly. Many thanks. I am very grateful.
My apologies for the delay in accepting the solution - I was out of the town.
0
 
Gustav BrockCIOCommented:
No problem. Have a nice weekend!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.