Solved

SELECT TOP n

Posted on 2000-01-18
Medium Priority
731 Views
Is there a way to have a query return the inverse of what a SELECT TOP n (or n%) would return?  For example if there are 100 records and I want those records, in alpha order, that are not the first 10 records.  SELECT NOT TOP 10 obviously not correct SQL but that's the kind of thing I want to do.  Is this possible?
0
• 7
• 3
• 3
• +2

LVL 2

Expert Comment

ID: 2365207
If you build the queries in code you could do the following:

Use a query to select all the records in REVERSE order.

Use another query to get the count of all the records.

Build a query in code using the information obtained form the previous two queries.  (i.e. Not top 10 would become TOP ((Num of records) - 10).)

Now resort the records.

This is what came of the top of my head right now.  Hope it helps!

Phil
0

LVL 4

Author Comment

ID: 2365277
Could you give some sample code for this?  You can just use SELECT... or SELECT TOP n... for the sql and I'll fill in my queries; I just want to see how the vb works.
0

LVL 7

Accepted Solution

lmerrell earned 200 total points
ID: 2365449

First create a Top 10 query (queryTop10 as below:

SELECT TOP 10 Table1.field1
FROM Table1 ORDER BY Table1.field1 DESC;

Then create a Find Unmatched Query using the Top 10 query and your table as follows:

SELECT [Table1].*
FROM Table1 LEFT JOIN queryTop10 ON [Table1].[field1] = [queryTop10].[field1]
WHERE ([queryTop1].[field1] Is Null);

lmerrell
0

LVL 7

Expert Comment

ID: 2365453
Let me know if you have questions.  It works well.

lmerrell
0

LVL 12

Expert Comment

ID: 2365789
lmerrell: This was my first though when I read the question text. To bad you beat me to it. This has to be easier than the other proposed method.
0

LVL 12

Expert Comment

ID: 2365790
By the way, Cool picture! :-)
0

LVL 7

Expert Comment

ID: 2368989
Or try this:

QueryTop10
----------
SELECT TOP 10 Table1.field1
FROM Table1 ORDER BY Table1.field1;

SELECT DISTINCT tbl1.field1
FROM tbl1, QueryTop10
WHERE (((tbl1.field1)<>[QueryTop10]![field1]))
ORDER BY tbl1.field1;
0

LVL 7

Expert Comment

ID: 2368997
The picture is cool, indeed :-)
0

LVL 7

Expert Comment

ID: 2369026
Sorry, mistaked, the statement must be:

SELECT tbl1.fld1
FROM tbl1
WHERE (tbl1.fld1 not in (SELECT TOP 10 tbl1.fld1
FROM tbl1.fld1
ORDER BY tbl1.fld1;))
ORDER BY tbl1.fld1;
0

LVL 4

Author Comment

ID: 2369027
lmerrell:
my query was a bit more complicated (it had some joins of its own) so your code was not directly applicable but the Find Unmatched Wizard was very helpful.
0

LVL 7

Expert Comment

ID: 2369033
Last edition:-)))

SELECT tbl1.fld1
FROM tbl1
WHERE (tbl1.fld1 not in (SELECT TOP 10 tbl1.fld1
FROM tbl1
ORDER BY tbl1.fld1;))
ORDER BY tbl1.fld1;
0

LVL 4

Author Comment

ID: 2369037
dedushka: i've awarded points already but thanks for the idea of nesting another query, that might come in handy.
0

LVL 7

Expert Comment

ID: 2369062
let us present this points to "expert of the week" - Imerrell is one of the best experts and I like to work together with him :-)
Cheers,
Dedushka
0

LVL 7

Expert Comment

ID: 2369069
let us present this points to "expert of the week" - Imerrell is one of the best experts and I like to work together with him :-)
Cheers,
Dedushka
0

LVL 7

Expert Comment

ID: 2369183
Thanks, Dedushka.  I haven't garnered many points recently as I've been swamped at work but I'll be back in full swing again soon.  And, adam923, I'm glad you got it working.

And thanks to all on the picture comments.  It was one of my least geeky.  ;-)

lmerrell
0

LVL 7

Expert Comment

ID: 2369191
Imerrell, #13 in Top 15 is not a good position, so we all hope to find you as #10 in few days :-)
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month4 days, 21 hours left to enroll