adam923
asked on
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Let me know if you have questions. It works well.
lmerrell
lmerrell
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.
By the way, Cool picture! :-)
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)<>[QueryTop
ORDER BY tbl1.field1;
QueryTop10
----------
SELECT TOP 10 Table1.field1
FROM Table1 ORDER BY Table1.field1;
SELECT DISTINCT tbl1.field1
FROM tbl1, QueryTop10
WHERE (((tbl1.field1)<>[QueryTop
ORDER BY tbl1.field1;
The picture is cool, indeed :-)
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;
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;
ASKER
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.
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.
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;
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;
ASKER
dedushka: i've awarded points already but thanks for the idea of nesting another query, that might come in handy.
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
Cheers,
Dedushka
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
Cheers,
Dedushka
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
And thanks to all on the picture comments. It was one of my least geeky. ;-)
lmerrell
Imerrell, #13 in Top 15 is not a good position, so we all hope to find you as #10 in few days :-)
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