HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

SQL/Access: Iterate through a query's results to use in another's WHERE clause

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlquerywhereiterateanotherthroughresultsuseclauseaccess

Problem

Just to preface, I'm pretty terrible with databases in general (patience appreciated).

I have a table structured as follows:

TEAM |PERSON |GAME |SCORE
_______|_______|_____|_____|
Apples |bob |A |30
Apples |bob |B |15
Apples |chris |A |13
Apples |chris |B |2
Oranges|sally |A |15
Oranges|sally |B |39
Oranges|tina |A |19
Oranges|tina |B |27
Bananas|larry |A |34
Bananas|larry |B |30
Bananas|harry |A |33
Banasas|harry |B |13
Bananas|charles|A |32
Bananas|charles|B |28

I would like to generate output that shows who scored the highest in each game and what team they are from.

Example:

GAME |TEAM |PERSON |SCORE
______|_______|________|_____|
A |bananas|larry |34
B |oranges|sally |39

Here is what I have so for:

I can grab the list of games via:

SELECT DISTINCT(GAME) from table;


And I can get the highest scorer from a specific game via:

SELECT TOP 1 GAME,TEAM,PERSON,SCORE from table WHERE GAME='A' ORDER BY SCORE DESC;


The part I am having trouble with using the results of the DISTINCT() query as part of the WHERE clause in my second query. Data is dynamic in my simple database.

Hopefully this can be solved with some SQL kung fu but:
If there is a better way to structure my database, I'm definitely open to those suggestions (right now, it's a single table database, data is ingested/overwritten daily, and dataset size is not expected to grow to problematic sizes, and I don't really know what I'm doing)

Thanks!

Solution

You can do this with a subquery to get your max score per game with the outside query pulling the details of the game.
FYI: This was tested in MS SQL, not MS-Access (sorry don't have that installed), but the syntax should be very similar.

SELECT t.* 
FROM tablename AS t 
JOIN (
    SELECT GAME, MAX(SCORE) AS MAXSCORE
    FROM tablename
    GROUP BY GAME
    ) AS MAXGAMES 
    ON ( t.GAME  = MAXGAMES.GAME 
     AND t.SCORE = MAXGAMES.MAXSCORE )
ORDER BY t.GAME ;

Code Snippets

SELECT t.* 
FROM tablename AS t 
JOIN (
    SELECT GAME, MAX(SCORE) AS MAXSCORE
    FROM tablename
    GROUP BY GAME
    ) AS MAXGAMES 
    ON ( t.GAME  = MAXGAMES.GAME 
     AND t.SCORE = MAXGAMES.MAXSCORE )
ORDER BY t.GAME ;

Context

StackExchange Database Administrators Q#206512, answer score: 2

Revisions (0)

No revisions yet.