NOT TOP X records

Topic

New Reply

I know I can get the top X records in a query using TOP, but is there an easy way to get everything else?

For example, I need to report the top 25 items and their associated $$ by category. I have that part down. Next I need the $$ for all the other items not in that top list…

is there an easier way than to pull it all except WHERE not in (TOP query) ??

Reply | Quote

Replies

is there an easier way than to pull it all except WHERE not in (TOP query) ??

I don’t think so.

Reply | Quote

I know I can get the top X records in a query using TOP, but is there an easy way to get everything else?

For example, I need to report the top 25 items and their associated $$ by category. I have that part down. Next I need the $$ for all the other items not in that top list…

is there an easier way than to pull it all except WHERE not in (TOP query) ??

Hi There

This is not the most elegant answer but it may get you out of a hole. I have created a dummy database with a table called tblList, in it were 10 records. From this table I created a table using the Make Table query or the following code:

[codebox]SELECT top 3 *
FROM tblList
INTO tblTop3;[/codebox]

I then created this bit of SQL

[codebox]
SELECT * from tblList

where id not in (select id from tblTop3);[/codebox]

It did produce the result….not elegant but could be a start of a solution

Reply | Quote

That’s almost exactly what I did, except I did not create a table. Instead I used the WHERE ID NOT IN (SELECT TOP 25….)

Worked like a charm!

Hi There

This is not the most elegant answer but it may get you out of a hole. I have created a dummy database with a table called tblList, in it were 10 records. From this table I created a table using the Make Table query or the following code:

I then created this bit of SQL
where id not in (select id from tblTop3);

It did produce the result….not elegant but could be a start of a solution

Reply | Quote