Querying for 2nd highest numeric value 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).
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]&lt;[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.