Top 10 query

[randolphoralph]

I am trying to get the top 10 from a query and can not seem to get what I need.

Table Name: New

Field Names:
GRP NAME (text)
GRP ID (text)
AMMS (text)
COUNT OF AMMS (Number)

I am trying to trying to display the Top 10 for each GRP NAME, GRP ID, AMMS based on the top 10 with the highest COUNT OF AMMS.

So as a before example...

GRP NAME GRP ID AMMS COUNT OF AMMS
SAD 879 FDD 15
SAD 879 ADF 14
SAD 879 FCD 13
SAD 879 ABC 12
SAD 879 GFD 11
SAD 879 GTH 10
SAD 879 HNJ 9
SAD 879 QDW 8
SAD 879 PJK 7
SAD 879 CVT 6
SAD 879 PLK 5
SAD 879 GOP 4
SAD 879 QMA 3
SAD 879 ZOA 2
SAD 879 CYS 1

After query for top 10 should look like this...

SAD 879 FDD 15
SAD 879 ADF 14
SAD 879 FCD 13
SAD 879 ABC 12
SAD 879 GFD 11
SAD 879 GTH 10
SAD 879 HNJ 9
SAD 879 QDW 8
SAD 879 PJK 7
SAD 879 CVT 6


It would be nice to figure out how to group GRP NAME GRP ID and AMMS at the same time.

 

[boblarson]

You'll need something like this.

 

[randolphoralph]

Ok I have created a duplicate of the table and named it Dupe and added the following SQL

SELECT New.[GRP NAME], New.[GRP ID], New.[AMMS], New.[CountOfAMMS]
FROM New
Where New.[GRP NAME] IN
     (SELECT TOP 10 [GRP NAME]
     FROM New AS Dupe
    WHERE Dupe.[GRP NAME[ = New.[GRP NAME]
    ORDER BY Dupe.[CountOfAMMS] DESC) 
ORDER BY New.[GRP NAME], New.[GRP ID], New.[AMMS], New.[CountOfAMMS];

When I am trying to save the query it is giving me a syntax error and states the issue is in this area

New.[GRP NAME] IN
     (SELECT TOP 10 [GRP NAME]
     FROM New AS Dupe
    WHERE Dupe.[GRP NAME[ = New.[GRP NAME]
    ORDER BY Dupe.[CountOfAMMS] DESC)

Not sure where I went wrong.

 

[boblarson]

1. You do NOT create a duplicate table. You only give it a NAME of Dup WITHIN the query.

2. Try again but this time WITHOUT the duplicate table and just using the convention he shows.

 

[randolphoralph]

Bob I removed the table and used the code

SELECT New.[GRP NAME], New.[GRP ID], New.[AMMS], New.[CountOfAMMS]
FROM New
Where New.[GRP NAME] IN
     (SELECT TOP 10 [GRP NAME]
     FROM New AS Dupe
    WHERE Dupe.[GRP NAME] = New.[GRP NAME]
    ORDER BY Dupe.[CountOfAMMS] DESC) 
ORDER BY New.[GRP NAME], New.[GRP ID], New.[AMMS], New.[CountOfAMMS];

The query for some reason is returning all the records. Maybe I missed something

 

[boblarson]

1. I wouldn't use the word NEW as a table or object name as it is a reserved word.

2. You should be using the group ID and not the name with this.

Also, I do believe you need to select the MAX of the CountOfAMMS for each group.

 

[randolphoralph]

Where would I incorporate MAX into this?

I will also update the table name as that is a reserved word. Thanks

 

[boblarson]

Where would I incorporate MAX into this?

I will also update the table name as that is a reserved word. Thanks

I think you would probably want the Max in the outer query but I'm not 100% sure.

 

[randolphoralph]

Well I have tried to using MAX inside and outside and it still does not give me the Top 10 by Category (GROUP ID).

 

[boblarson]

Can you post a sample of your two tables (with bogus data is fine as long as it would be similar to what you really have) so I can play with it?

 

[randolphoralph]

Here is a sample with a Before and After Table.

 

[boblarson]

See if this works for you:

SELECT TOP 10 BeforeData.[GRP ID], BeforeData.[GRP NAME], BeforeData.CountOfAMMS, BeforeData.AMMS
FROM BeforeData
WHERE (((BeforeData.CountOfAMMS) In (SELECT TOP 1 [CountOfAMMS]
      FROM BeforeData AS Dupe
     WHERE Dupe.[AMMS] = BeforeData.[AMMS]     
     ORDER BY Dupe.[CountOfAMMS] DESC)))
ORDER BY BeforeData.CountOfAMMS DESC , BeforeData.AMMS, BeforeData.CountOfAMMS;

 

[randolphoralph]

That works great! Thanks Bob you have no idea how you have made my day. I have been driving myself crazy since yesterday trying to figure it out.

 

[randolphoralph]

The one thing I noticed after trying this on the sample database is that if I add different grp names. Then it will only pull the Top 10 out of all the records instead of the Top 10 from each group.

See attachment.

 

[boblarson]

Okay, I think I finally got it for sure this time:

SELECT BeforeData.[GRP ID], BeforeData.[GRP NAME], BeforeData.CountOfAMMS, BeforeData.AMMS
FROM BeforeData
WHERE (((BeforeData.CountOfAMMS) In (SELECT TOP 10 [CountOfAMMS]
      FROM BeforeData AS Dupe
     WHERE Dupe.[GRP NAME] = BeforeData.[GRP NAME]     
     ORDER BY Dupe.[CountOfAMMS] DESC)))
ORDER BY BeforeData.CountOfAMMS DESC , BeforeData.AMMS;

 

[randolphoralph]

You are good Bob! That works perfectly. Thanks for putting up with me.

 

[boblarson]

WHEW! Glad we got through that one. Subqueries like that I haven't had to do many of them so even I got a little bit of instruction from that. So thanks for letting me help.