Top 10 Longest Games In Hours

[jonath...@gmail.com]

My first REAL query using the Retrosheet database.  I went overboard obviously since this probably could have been written on one line ( select * from games order by minutes_game_ct desc limit 10; )  but I was having fun formatting the results.

I commented parts of the query for explanations in case anybody isn't familiar with some of the MySQL functions that I used. If you copy the code into a text editor with syntax highlighting the comments will be more visible.

Please feel free to share any criticisms and correct any problems that you find.  Also, let me know if you have any questions about why or how I did something.

# top 10 longest games (hours)

select   

cast(concat

  (

      substr(game_id,4,4),

      '-',substr(game_id,8,2),

      '-',substr(game_id,10,2)

  ) as date) as 'Game Date', # extracts date and converts to date format YYYY-MM-DD

  inn_ct as 'Innings', 

  sec_to_time(minutes_game_ct*60) as 'Game Length', # converts minutes to HH:MM:SS

  concat(away_team_id,' @ ',home_team_id) as 'Teams', # creates "away @ home" string

  case 

    when

      home_score_ct > away_score_ct 

    then home_team_id

    else away_team_id

    end as 'Winner',

  case 

    when 

      home_score_ct > away_score_ct 

    then 

      concat(home_score_ct, ' - ',away_score_ct)

    else

      concat(away_score_ct, ' - ',home_score_ct)

    end as 'Score' # places winning team score first, loser second

  #, games.* # commented out but available if needed

from games

where inn_ct > 9 # exclude two games that have bad minutes_game_ct data ('HOU196207212','MON199106280')

order by minutes_game_ct desc

limit 10;