SELECT TOP n

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?
LVL 4
adam923Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

phil2freeCommented:
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
adam923Author Commented:
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
lmerrellCommented:
adam923,

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

lmerrell
0
TrygveCommented:
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
TrygveCommented:
By the way, Cool picture! :-)
0
DedushkaCommented:
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
DedushkaCommented:
The picture is cool, indeed :-)
0
DedushkaCommented:
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
adam923Author Commented:
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
DedushkaCommented:
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
adam923Author Commented:
dedushka: i've awarded points already but thanks for the idea of nesting another query, that might come in handy.
0
DedushkaCommented:
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
DedushkaCommented:
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
lmerrellCommented:
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
DedushkaCommented:
Imerrell, #13 in Top 15 is not a good position, so we all hope to find you as #10 in few days :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.