Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 <Category> not in (TOP query) ??
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Abraxus' post='762288' date='27-Feb-09 18:24']is there an easier way than to pull it all except WHERE <Category> not in (TOP query) ??[/quote]
    I don't think so.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Abraxus' post='762288' date='27-Feb-09 17:24']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 <Category> not in (TOP query) ??[/quote]

    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
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!


    [quote name='Jezza' post='762296' date='27-Feb-09 12:13']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[/quote]
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •