I'm working on a baseball related website. I have a table with a batting lineup for two baseball teams:
+----+----------+--------------+--------+
| id | playerId | battingOrder | active |
+----+----------+--------------+--------+
Batting order is an integer between 1 and 20. This corresponds to the following logic:
The active field is a tinyint 0 or 1, representing the pitcher on the mound and the batter on the plate.
Known Fact: There will always be one active pitcher from one team and one active batter from the opposite team.
I need to write a query that returns a row for a home team player that corresponds to the next batter in the battingOrder. (the one that that occurs after the active batter's battingOrder)
Example:
I've never used a CASE query before, but I came up with the following:
SELECT *
FROM lineups
WHERE battingOrder =
CASE (
SELECT battingOrder
FROM lineups
WHERE battingOrder > 10 AND active = 1
LIMIT 1
)
WHEN 11 THEN 12
WHEN 12 THEN 13
WHEN 13 THEN 14
WHEN 14 THEN 15
WHEN 15 THEN 16
WHEN 16 THEN 17
WHEN 17 THEN 18
WHEN 18 THEN 19
WHEN 19 THEN 11
END
LIMIT 1;
It seems to work, but what edge cases and/or pitfalls have I walked into? Is this efficient? I'm particulary interested in a solution to my problem that does not use a nested query.
Select LNext.player As NextPlayer
From lineups As L
Left Join lineups As LNext
On LNext.BattingOrder Between 11 And 20
And LNext.BattingOrder = Case
When L.BattingOrder = 19 Then 11
Else L.BattingOrder + 1
End
Where L.battingOrder Between 11 And 20
And L.active = 1
In fact, you could make it handle both home and away like so:
Select LNext.player As NextPlayer
From lineups As L
Left Join lineups As LNext
On LNext.BattingOrder = Case
When L.BattingOrder = 19 Then 11
When L.BattingOrder = 9 Then 1
Else L.BattingOrder + 1
End
Where L.active = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With