new_wes
asked on
Select Top 5 For Each Group - subquery syntax bug
Hi all - painfully stumped for a Friday. Trying to implement any of these three previous solutions to 'selecting top x for each group' type problem: Select top members with most claims total for each group (Last_Name).
Member_ID Last_Name Total
001 Palmetto 12
002 Trident 10
003 Palmetto 9
004 Macy 4
005 Macy 1
006 Macy 9
Result (for Top 1):
001 Palmetto 12
002 Trident 10
006 Macy 9
Templates:
https://www.experts-exchange.com/questions/21464877/Top-5-of-top-5.html
https://www.experts-exchange.com/questions/20729610/Why-can't-do-this-Subquery.html
https://www.experts-exchange.com/questions/10699721/query-for-last-two-unique-values-by-descending-date.html (paasky's 12:30 comment)
all of which use basic subquery structure which i copied to use below like:
SELECT MEMBER_ID, LAST_NAME, TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE TOTAL IN
(SELECT TOP 2 Q2.TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE Q1.LAST_NAME = Q2.LAST_NAME
ORDER BY Q2.CLAIMS DESC)
and which is causing me endless grief in Access2000 - either undefined syntax err, does not recognize 'Q1.LAST_NAME' as valid, or missing 'EXSISTS' instead of where in - none of which i can get to work.
any ideas??
thanks, wes
Member_ID Last_Name Total
001 Palmetto 12
002 Trident 10
003 Palmetto 9
004 Macy 4
005 Macy 1
006 Macy 9
Result (for Top 1):
001 Palmetto 12
002 Trident 10
006 Macy 9
Templates:
https://www.experts-exchange.com/questions/21464877/Top-5-of-top-5.html
https://www.experts-exchange.com/questions/20729610/Why-can't-do-this-Subquery.html
https://www.experts-exchange.com/questions/10699721/query-for-last-two-unique-values-by-descending-date.html (paasky's 12:30 comment)
all of which use basic subquery structure which i copied to use below like:
SELECT MEMBER_ID, LAST_NAME, TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE TOTAL IN
(SELECT TOP 2 Q2.TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE Q1.LAST_NAME = Q2.LAST_NAME
ORDER BY Q2.CLAIMS DESC)
and which is causing me endless grief in Access2000 - either undefined syntax err, does not recognize 'Q1.LAST_NAME' as valid, or missing 'EXSISTS' instead of where in - none of which i can get to work.
any ideas??
thanks, wes
ASKER
hmm - pretty nice, but actually need to see top 20-25 for each Last_Name - can't think of any way to adjust yours for that?
oh, and also, 100's of Last_Name 's when grouped, so no union queries for each group either. only about 50k records overall currently, shouldn't go over few mil in long run.
oh, and also, 100's of Last_Name 's when grouped, so no union queries for each group either. only about 50k records overall currently, shouldn't go over few mil in long run.
there's Always a way!! lol
SELECT TOP 25 [_Top2].lastname, Max([_Top2].total) AS MaxOftotal FROM _Top2 GROUP BY [_Top2].lastname;
SELECT TOP 25 [_Top2].lastname, Max([_Top2].total) AS MaxOftotal FROM _Top2 GROUP BY [_Top2].lastname;
this pulls the highest for joe, then the highest for sam, and the highest for tom...etc...top 25 being the highest totals.
This one sorts it highest to lowest
SELECT TOP 25 [_Top2].lastname, Max([_Top2].total) AS MaxOftotal FROM _Top2 GROUP BY [_Top2].lastname ORDER BY Max([_Top2].total) DESC;
This one sorts it highest to lowest
SELECT TOP 25 [_Top2].lastname, Max([_Top2].total) AS MaxOftotal FROM _Top2 GROUP BY [_Top2].lastname ORDER BY Max([_Top2].total) DESC;
Let me be sure I understand correctly...patients for the feeble minded.
You have bob in your table 100 times. He's got 100 values. you want bob's top 25, then you move to sam, etc.. Cause if that's what you are trying to do...then I'm tupid. I didn't do it like that.
J
You have bob in your table 100 times. He's got 100 values. you want bob's top 25, then you move to sam, etc.. Cause if that's what you are trying to do...then I'm tupid. I didn't do it like that.
J
Did you try:
SELECT MEMBER_ID, LAST_NAME, TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE TOTAL IN
(SELECT TOP 2 Q2.TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE Q1.LAST_NAME = Q2.LAST_NAME
ORDER BY Q2.CLAIMS DESC)
I think access doesn't like AS for qualifying tables...
Nic;o)
SELECT MEMBER_ID, LAST_NAME, TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE TOTAL IN
(SELECT TOP 2 Q2.TOTAL
FROM q_RxSav_7s2_MembersClaimsA
WHERE Q1.LAST_NAME = Q2.LAST_NAME
ORDER BY Q2.CLAIMS DESC)
I think access doesn't like AS for qualifying tables...
Nic;o)
ASKER
just realized same thing! i need top 25 for sam, then top 25 for bob etc. i think that's what they were trying to do with subquery: pull each group, and then for each group pull top 25 where group=group . . .
thanks for trying though!
thanks for trying though!
you're going to have to code this one.
list of names
get first name
Get top 25 values for this name
Put them somewhere
get next name
loop
something like that.
list of names
get first name
Get top 25 values for this name
Put them somewhere
get next name
loop
something like that.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi nico - thanks for the link - it came in handy.
harfang, it was your comment about incompatible subqueries that saved that day! When i take the source query and change it to make-table (because it's based on about 3 layers of other queries), then run the "top" query on that resulting table instead of the source query, it runs fine!! quite frustrated with the lack of any error messages from access, but then again, realize it's probably doing more than expected, so will just have to live with that!
thanks very much all -
wes
harfang, it was your comment about incompatible subqueries that saved that day! When i take the source query and change it to make-table (because it's based on about 3 layers of other queries), then run the "top" query on that resulting table instead of the source query, it runs fine!! quite frustrated with the lack of any error messages from access, but then again, realize it's probably doing more than expected, so will just have to live with that!
thanks very much all -
wes
Thank you, new_wes.
As a matter of fact, using temp tables is a very common technique on large databases, and it's often the fastest (or the only) way. The reason is that relatively simple queries can be optimized, while a "four layers" query (if I count correctly) has little or no chance to be optimizable. If you use the temp table for more that just a one-pass operation, it also makes sense to index relevant fields (using a data-definition query for instance).
So it seems you chose the best path in your case. Good luck with your project!
(°v°)
As a matter of fact, using temp tables is a very common technique on large databases, and it's often the fastest (or the only) way. The reason is that relatively simple queries can be optimized, while a "four layers" query (if I count correctly) has little or no chance to be optimizable. If you use the temp table for more that just a one-pass operation, it also makes sense to index relevant fields (using a data-definition query for instance).
So it seems you chose the best path in your case. Good luck with your project!
(°v°)
ASKER
in this case last night i remembered that one of the layers was a 'crosstab', and in hindsight building anything on top of that was probably not wise. given your advice, i'll assume that might be best place to add a 'make-table' layer, then work from there.
thanks for the info - always like to learn!
thanks for the info - always like to learn!
Yes, that is the best place. A crosstab as such can be an optimized query, but anything based on it will not be. The Engine will have to create the table in memory anyway... Then again, the resulting table will not be normalized anymore, hence with very limited query potential.
I normally set things up so that the cross-tab is the very last stage. Not to mention the fact that your sample data does not seem to require a cross-tab anywhere as previous layer. In fact, it looks as though you should actually use the raw tables as source, not any prior query. That a general advice, btw. Try as often as possible to create new queries based on the source. This is both for efficiency and maintenance reasons.
(°v°)
I normally set things up so that the cross-tab is the very last stage. Not to mention the fact that your sample data does not seem to require a cross-tab anywhere as previous layer. In fact, it looks as though you should actually use the raw tables as source, not any prior query. That a general advice, btw. Try as often as possible to create new queries based on the source. This is both for efficiency and maintenance reasons.
(°v°)
SELECT [Table].lastname, Max([Table].total) AS MaxOftotal FROM Table GROUP BY [Table].lastname;