CODEWITHSUNDEEP
sqlsqlite
Dylan Benton
Dylan Benton

Reputation: 109

sqlite Joins with MAX

I have 2 tables. One displays a game played (Date,Where, result,opponent etc) and the other one the details of a batting innings (runs scored, etc) Both tables have a primary key that relates the batting back to a specific game.

I am trying to return the OPPONENT column from Games when the MAX (highest) score is recorded in the table BATTING, but currently i am unsure how to do this.

The 2 tables can be found here

https://i.sstatic.net/rGaK9.png

The example from these tables would be (max score is 101 in RUNSSCORED, so return the linked OPPONENT from GAMEINDEX which is "Ferndale"

Any help would be great. Thanks.

Upvotes: 0

Views: 768

Answers (2)

kapandron
kapandron

Reputation: 3671

SELECT G.OPPONENT, MAX(B.RUNSSCORED)
FROM GAMES AS G 
     INNER JOIN BATTING AS B
     ON G.GAMESINDEX = B.GAMESINDEX

Upvotes: 0

Fabian
Fabian

Reputation: 2982

Is this what you are looking for?

select OPPONENT 
from GAMES 
where GAMESINDEX in 
  (select GAMESINDEX from BATTING order by RUNSSCORED desc limit 1);

If there isn't a unique max RUNSSCORED value, then the answer might not be deterministic.

If you want multiple winners in that case, you could use

select OPPONENT  
from GAMES natural join BATTING 
WHERE RUNSSCORED in (select MAX(RUNSSCORED) from BATTING);

Upvotes: 1

Related Questions