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

sql ms-access jet
Joel Spolsky picture Joel Spolsky · Feb 5, 2009 · Viewed 18.6k times · Source

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

Wayne Molina picture Wayne Molina · Feb 5, 2009

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 :)

Related questions

How to get id of newly inserted record using Excel VBA?

Seems a common enough problem this, but most solutions refer to concatenating multiple SQL commands, something which I believe can't be done with ADO/VBA (I'll be glad to be shown wrong in this regard however). I currently insert my …

Read More
Multiple LEFT JOIN in Access

I have the following query, which works for MySQL: DELETE `test1`, `test2`, `test3`, `test4` FROM `test1` LEFT JOIN `test2` ON test2.qid = test1.id LEFT JOIN test3 ON test3.tid = test2.id LEFT JOIN test4.qid = test1.id WHERE test1.…

Read More
Table-less UNION query in MS Access (Jet/ACE)

This works as expected: SELECT "Mike" AS FName This fails with the error "Query input must contain at least one table or query": SELECT "Mike" AS FName UNION ALL SELECT "John" AS FName Is this just a quirk/limitation of …

Read More