1

Database set up here (was too large for sqlfiddle): https://pastebin.com/Hd0DbQ5z

This is an MS Access DB.

I am looking to get the top score as well as the second top score. I have the following query for getting the top score which is the exact results I am looking for:

SELECT e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN 
(SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

I would like to tweak this query to show me the 2nd place scores, here is the closest I got (but with some unexpected results):

SELECT e.GAME_VER, e.CLAN, e.SCORE, e.S_REGION
FROM dev AS e 
INNER JOIN (SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' AND ID NOT IN ( SELECT ID FROM dev as f INNER JOIN  (SELECT GAME_VER, MAX(SCORE) AS MAXVAL2 FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER) AS MAXSCORES2 ON  (f.SCORE = MAXSCORES2.MAXVAL2) AND (f.GAME_VER = MAXSCORES2.GAME_VER)) GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

(Sorry for the formatting, the query is kind of messy)

This query gives me the results that I am looking for, but it is returning rows from different S_REGION values even though I have them constrained to a single region in the query.

+----------+---------+----------+----------+
| GAME_VER |  CLAN   |  SCORE   | S_REGION |
+----------+---------+----------+----------+
|     3.25 | Melons  | 93.97925 | USA      |
|    3.375 | Melons  | 94.96925 | USA      |
|      3.5 | Bananas |   95.883 | USA      |
|    3.625 | Grapes  |    96.74 | USA      |
|     3.75 | Bananas |   97.503 | USA      |
|    3.875 | Grapes  |   98.354 | USA      |
|     3.99 | Lemons  |   96.777 | USA      |
|        4 | Grapes  |   99.092 | USA      |
|    4.125 | Grapes  |   99.698 | USA      |
|     4.25 | Grapes  |   100.39 | USA      |
|    4.375 | Grapes  |  100.994 | USA      |
|    4.375 | Apples  |  100.994 | PANAMA   |
|      4.5 | Bananas |  101.618 | USA      |
|    4.625 | Bananas |   102.22 | USA      |
|     4.75 | Bananas |  102.804 | USA      |
|    4.875 | Bananas |  103.342 | USA      |
|     4.99 | Lemons  |  101.619 | USA      |
|        5 | Bananas |  103.823 | USA      |
|    5.125 | Bananas |  104.428 | USA      |
|     5.25 | Bananas |  105.014 | USA      |
|    5.375 | Bananas |   105.49 | USA      |
|      5.5 | Bananas |  105.944 | USA      |
|    5.625 | Grapes  |  106.379 | USA      |
|     5.75 | Grapes  |    106.5 | USA      |
|     5.75 | Grapes  |    106.5 | CANADA   |
+----------+---------+----------+----------+

What is going wrong? Or is there a better way of writing the query to get the results I'm looking for. (I'm a noob).

  • The linked-to schema is not correct. You have int data types for decimals numbers. – C Perkins Jul 7 '18 at 16:30
1

Regarding the mismatched regions: Only the subqueries (i.e. the innermost aggregate queries) are constrained. Such constraints do not directly limit the outer query. So if a score from another S_REGION matches a score from the constrained region, then it will also be returned in the outer query. That is unless you further constrain the outer query to have the same constraints.

This is true even of your first query even though you might not have noticed: If there ever is another region with tied top scores, you would get values from the other regions also. Try it... add a row with another region matching a USA high score and it will appear in your first top-score query.

Perhaps more useful would be to treat REGION and S_REGION exactly as you have GAME_VER in that you group by these values in all subqueries, then only add specific constraints in the final query. Not only would this fix your problem of returning mismatched regions, it would easily allow you to view top scores from any region simply by removing or changing the constraints only on the outer query. This is what I'll do in the examples below.


Embedded subqueries are convenient for posting on Stack Overflow and for keeping everything together in one place. However, Access is not a fully optimized SQL engine. It neither creates nor stores optimized execution plans for each subquery. For subqueries in the WHERE clause, it will almost always re-execute each subquery for every row, even if theoretically it could run it just once and then refer back to the same temporary dataset.

Access does a better job at optimization of saved queries. Thus, I recommend "saving subqueries" then using JOINS on the saved query rather than embedded SQL. It allows easier reuse of the subquery and within Access it allows the subquery to be edited and viewed like a normal query.


With all that in mind, here's my suggested solution:

Saved query MAXSCORES:

SELECT e.REGION, e.S_REGION, e.GAME_VER, Max(e.SCORE) AS MAXVAL
FROM dev AS e
GROUP BY e.REGION, e.S_REGION, e.GAME_VER;

Replacement for the first query of top scores (properly filtered on desired region):

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAXSCORES 
    ON (e.S_REGION = MAXSCORES.S_REGION) AND (e.REGION = MAXSCORES.REGION) 
      AND (e.GAME_VER = MAXSCORES.GAME_VER) AND (e.SCORE = MAXSCORES.MAXVAL)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN;

The 2nd place scores could be obtained just how you did it (of course updated with appropriate constraints), but as already stated, I avoid embedded subqueries in the WHERE clause. Further, the following query not only returns the 2nd place scores, but it also indicates which groups did not have any 2nd place score--it returns null values. Lastly, this query returns the 1st and 2nd place scores in one query on the same row which could be convenient for various comparison queries.

Saved query MAX2NDSCORE:

SELECT e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL, 
  Max(IIf([SCORE]<[mx].[MAXVAL],[SCORE],Null)) AS MAX2NDVAL
FROM dev AS e LEFT JOIN MAXSCORES AS mx 
    ON (e.REGION = mx.REGION) AND (e.S_REGION = mx.S_REGION) 
      AND (e.GAME_VER = mx.GAME_VER)
GROUP BY e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL;

A query that returns 2nd place clans for the desired region:

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAX2NDSCORES AS mx2
    ON (e.SCORE = mx2.MAX2NDVAL) AND (e.GAME_VER = mx2.GAME_VER)
      AND (e.S_REGION = mx2.S_REGION) AND (e.REGION = mx2.REGION)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN

Finally, its worth stating that these queries do not properly consider or identify tied scores, but there is nothing in the question or table schema that suggests there cannot be tied scores. The queries could return multiple clans that have the same score.

|improve this answer|||||
  • Thank you so much! This has given me a lot to think about. For the tied scores scenario, I added another clan with the same score as one in the same S/REGION and sure enough there was a second row of that GAME_VER. It makes sense to allow duplicates in the schema so would you be able to point me in the right direction in how to deal with these tied scores? (Picking one over the other in the case of a tie is not a concern, the GAME_VER column must be unique for the report I'm trying to generate). Thanks! – Chip Shadd Jul 10 '18 at 4:57
  • 1
    If GAME_VER must be unique, do you even need the CLAN? The query I labeled MAX2NDSCORE already returns unique GAME_VER (per every REGION and S_REGION). If you do need at least one arbitrary clan, you could use the last query, but group on on REGION, S_REGION, GAME_VER, then select FIRST(e.CLAN) and FIRST(e.SCORE) to avoid multiple rows for ties. (Since it's arbitrary, you could also use LAST() aggregate function.) – C Perkins Jul 10 '18 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.