LeeZee LeeZee - 6 months ago 21
SQL Question

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;

Answer

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.