Search code examples
mysqlresetcalculated-columnsrow-number

trying to create a "Row Number" column that restarts from 1 again at the start of a new year and when there is new stating pitcher

I'm struggling to determe the MySQL code to create a Row_Number column in my table "starting_pitcher_stats" that I'd like to start from 1 but then restart from 1 at the beginning of a new year and when there is a new pitcher. I used the following code to create the Row_Number column:

Ideally, the table would look like this:

    Starting_Pitcher park_factor  std_PF  Row_Number  Game_Date  Game_Number
    aased001          108         108     1           1977-07-26     0
    aased001          94          101     2           1977-07-31     0
    aased001          100         100.66  3           1977-08-06     0
    aased001          108         102.5   4           1977-08-11     0
    aased001          108         103.66  5           1977-08-16     0
    aased001          96          102.33  6           1977-08-21     0
    aased001          108         103.14  7           1977-08-26     0
    aased001          108         103.75  8           1977-08-31     0
    aased001          104         103.77  9           1977-09-05     1
    aased001          108         104.2   10          1977-09-10     0
    aased001          92          103.09  11          1977-09-16     0
    aased001          106         103.33  12          1977-09-22     0
    aased001          108         103.69  13          1977-09-27     1
    aased001          96          96      1           1978-04-11     0
    aased001          100         13.06   2           1978-04-16     0
    aased001          100         18.5    3           1978-04-21     0
    aased001          96          23.05   4           1978-04-28     0

...As it is now, a sample of the table looks like this:

    Starting_Pitcher park_factor  std_PF  Row_Number  Game_Date  Game_Number
    aased001          108         108     1           1977-07-26     0
    aased001          94          101     2           1977-07-31     0
    aased001          100         100.66  3           1977-08-06     0
    aased001          108         102.5   4           1977-08-11     0
    aased001          108         103.66  5           1977-08-16     0
    aased001          96          102.33  6           1977-08-21     0
    aased001          108         103.14  7           1977-08-26     0
    aased001          108         103.75  8           1977-08-31     0
    aased001          104         103.77  9           1977-09-05     1
    aased001          108         104.2   10          1977-09-10     0
    aased001          92          103.09  11          1977-09-16     0
    aased001          106         103.33  12          1977-09-22     0
    aased001          108         103.69  13          1977-09-27     1
    aased001          96          96      14          1978-04-11     0
    aased001          100         13.06   15          1978-04-16     0
    aased001          100         18.5    16          1978-04-21     0
    aased001          96          23.05   17          1978-04-28     0

and I used to following code to create it:

ALTER TABLE starting_pitcher_stats ADD Row_Number int(11) DEFAULT '0' NOT NULL;
    SELECT @n:=0, Row_Number, Starting_Pitcher, lg_ID, YEAR_ID, Game_Date, Game_Number FROM starting_pitcher_stats;
    UPDATE starting_pitcher_stats SET Row_Number = @n := @n + 1

When I use the following code to make the Row_Number column restart at 1 at the start of a new year and when there is a new pitcher, it doesn't work:

ALTER TABLE starting_pitcher_stats ADD ROW_NUMBER1 int(11) DEFAULT '0' NOT NULL;
SELECT @n:=0, Row_Number, Starting_Pitcher, lg_ID, YEAR_ID, Game_Date, Game_Number FROM starting_pitcher_stats;
UPDATE starting_pitcher_stats IF std_PF=park_factor THEN SET Row_Number=1 ELSE SET Row_Number1 = @n := @n + 1

I get the following error: 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF std_PF=park_factor THEN SET Row_Number=1 ELSE SET Row_Number' at line 1

Is it possible to set up this Row_Number column such that it will start at 1 even when I reorder it (or group it) by another column like the Game_Date column?

Can someone please assist with this?

Thank you in advance. Lee

UPDATE: Gordon, here is the error I get: 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1, 1)
                         )
               ) as rn
        from starting_p' at line 4

I'm sure the edit required is evident, but my experience is not enough to be able to spot it.

Yes, there is a unique ID "GAME_ID" that encapsulates "YEAR_ID", "Game_Date", "Game_Number", and home team for that game "Park_ID". Thanks for reminding me about double_headers..."Game_Number" is a field that I had derived out of the "GAME_ID" field that is available and refers to game "1" or game "2" of a double-header if there was one or Game "0" if a single game. It sounds like using GAME_ID would make the process more efficient, rather than joining separately by Year_ID, Game_Date, and Game Number?

Here is a screen-shot of a sample of the table, this time including the GAME_ID and YEAR_ID columns:

enter image description here

I'm still trying to understand all of the code...Does "sy" from "@sy" have to be defined somewhere?

I Thank you in advance for your help. Lee

UPDATE: Here's the error I received when I tried the code that I had edited by removing a parenthesis and changing the names of some of the fields to match my table: 

Incorrect integer value: 'aased001:1977:1:1' for column 'row_number' at row 1

Here's latest code I used that yielded the above error. I hope I didn't inadvertently take the code further away than what it was supposed to accomplish:

UPDATE starting_pitcher_stats JOIN
           (select starting_pitcher_stats.*, 
                   (@rn := if(@sy = concat_ws(':', Starting_Pitcher, YEAR_ID), @rn + 1,
                              @sy := concat_ws(':', Starting_Pitcher, YEAR_ID, 1, 1)
                             )
                   ) as rn
            from starting_pitcher_stats
            cross join
                 (select @rn := 0, @sy := '') AS params
            order by Starting_Pitcher, YEAR_ID, Game_Date, Game_Number
           ) as b
           on b.Starting_Pitcher = starting_pitcher_stats.Starting_Pitcher AND
              b.YEAR_ID = starting_pitcher_stats.YEAR_ID AND
              b.Game_Date = starting_pitcher_stats.Game_Date AND
              b.Game_Number=starting_pitcher_stats.Game_Number
        set starting_pitcher_stats.row_number= b.rn

UPDATE: Here is the code that worked without error:

UPDATE starting_pitcher_stats JOIN
       (select starting_pitcher_stats.*,
               (@rn := if(@sy = concat_ws(':', Starting_Pitcher, YEAR_ID), @rn + 1,
                          if(@sy := concat_ws(':', Starting_Pitcher, YEAR_ID), 1, 1)
                         )
               ) as rn
        from starting_pitcher_stats CROSS JOIN
             (select @rn := 0, @sy := '') params
        order by Starting_Pitcher, YEAR_ID, Game_Date, Game_Number
       ) sp2
       on sp2.Starting_Pitcher = starting_pitcher_stats.Starting_Pitcher AND
          sp2.YEAR_ID = starting_pitcher_stats.YEAR_ID AND
          sp2.Game_Date = starting_pitcher_stats.Game_Date AND
          sp2.Game_Number=starting_pitcher_stats.Game_Number
    set starting_pitcher_stats.row_number = sp2.rn;
Solution

  • It is a bit of a pain to do the enumeration in an update. But it is possible.

    In your case this is probably simplest using a JOIN with a subquery:

    update starting_pitcher sp JOIN
       (select sp.*,
               (@rn := if(@sy = concat_ws(':', starting_pitcher, year), @rn + 1,
                          if(@sy := concat_ws(':', starting_pitcher, year), 1, 1)
                         )
               ) as rn
        from starting_pitcher_stats cross join
             (select @rn := 0, @sy := '') params
        order by starting_pitcher, year, game_date
       ) sp2
       on sp2.starting_pitcher = sp.starting_pitcher and
          sp2.year = sp.year and
          sp2.game_date = sp.game_date
    set sp.row_number = sp2.rn;

    Note: This uses the three columns (starting_pitcher, year, game_date) for the join. If you have a unique id in the table, that is better. In particular, your sample does not have double headers. So, you might want to add additional fields. The on conditions are simply to match one row to the same row in the subquery.

    You can see what is happening by running the subquery separately.