Select Top (all but 10) from ... in Microsoft Access

Question

Say I've got a query

SELECT TOP 10 ... FROM ... ORDER BY ...

in Access (well, really Jet). The question is: how can I get all the other rows... everything except the top 10?

Answer 1

Couldn't you do something like

SELECT ... FROM ...
WHERE PK NOT IN (SELECT TOP 10 PK FROM ...)
ORDER BY ...

it might not be that efficient but that's the only way off the top of my head I can think to do something like that. AFAIK there's no "BOTTOM" clause in SQL :)

Answer 2

SELECT ... FROM ....
WHERE myID NOT IN 
    (SELECT TOP 10 myID FROM ... ORDER BY rankfield)
ORDER BY sortfield

Note that your sorted order could, (if you wish) be different than your ranked order.

Edit: Another idea: If you already knew how many TOTAL rows were there, you could do (assuming 1000 rows):

SELECT TOP 990 ... FROM .... ORDER BY sortfield DESC

Just flip the sort, and take the remaining portion.

Of course, if you still wanted the results in the original order, you'd have to do something silly like:

SELECT ... 
FROM (SELECT TOP 990 ... FROM .... ORDER BY sortfield DESC)
ORDER BY sortfield ASC

Answer 3

You can create a rank field (Ways to Create Rank Column) and filter off of that: where rank >10

Answer 4

This it something that is often better done on the 'client' side, rather on the DBMS i.e. fetch all the table's rows into an ADO Classic recordset then use the Filter property to remove the 10 rows based on criteria, or Sort then set then skip the first/last 10 rows, or set the page length as appropriate then skip the first/last page, etc. Not only does it depend on the number of rows but also the target application e.g. I know that if this is the data source for a MS Access report then filtering off the unwanted rows can be a lot of hassle.