Refresh table based on minimum date in group
I have the following table diagram representing some of the players and the teams they play for:
CREATE TABLE PLAYERS
(
NAME VARCHAR(64) NOT NULL,
BIRTHDAY TIMESTAMP NOT NULL,
TEAM VARCHAR(64) NOT NULL,
CAPTAIN BOOLEAN
);
My data looks like
"PLAYER1","1998-02-13 00:00:00","TEAM_A",NULL
"PLAYER2","1984-01-13 00:00:00","TEAM_A","1"
"PLAYER3","1985-07-13 00:00:00","TEAM_A",NULL
"PLAYER4","1979-08-13 00:00:00","TEAM_B",NULL
"PLAYER5","1986-09-13 00:00:00","TEAM_B",NULL
"PLAYER6","1990-11-13 00:00:00","TEAM_B",NULL
"PLAYER7","1993-12-13 00:00:00","TEAM_C",NULL
"PLAYER8","1987-05-13 00:00:00","TEAM_C",NULL
"PLAYER9","1995-04-13 00:00:00","TEAM_C",NULL
Now I have a requirement that each team needs exactly one captain. TEAM_A already has one (PLAYER2), but TEAM_B and TEAM_C do not. So I need an SQl script that identifies the oldest players on the same team and sets the captain flag for them. Can someone please help me on this.
source to share
I would start by getting the oldest player for each team without a captain like this:
SELECT team, MIN(birthday) AS minBirthday
FROM myTable
WHERE team NOT IN (SELECT DISTINCT team FROM myTable WHERE captain = 1)
GROUP BY team;
After that, you can use it to update the captains with a JOIN:
UPDATE myTable m
JOIN(
SELECT team, MIN(birthday) AS minBirthday
FROM myTable
WHERE team NOT IN (SELECT DISTINCT team FROM myTable WHERE captain = 1)
GROUP BY team) t ON t.team = m.team AND t.minBirthday = m.birthday
SET m.captain = 1;
As written, this will set two captains if two players have the same minimum birthday. If you have another tiebreak, you can set up an internal request to select the correct player and adjust the connection if necessary. Below is a SQL Fiddle example .
source to share
Updated to no longer be SQL Server code and learned MySQL for the first time.
CREATE TABLE PLAYERS
(
NAME VARCHAR(64) NOT NULL,
BIRTHDAY DATETIME NOT NULL,
TEAM VARCHAR(64) NOT NULL,
CAPTAIN BIT
);
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1998-02-13 00:00:00',NULL,'TEAM_A','PLAYER1';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1984-01-13 00:00:00',1,'TEAM_A','PLAYER2';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1985-07-13 00:00:00',NULL,'TEAM_A','PLAYER3';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1979-08-13 00:00:00',NULL,'TEAM_B','PLAYER4';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1986-09-13 00:00:00',NULL,'TEAM_B','PLAYER5';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1990-11-13 00:00:00',NULL,'TEAM_B','PLAYER6';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1993-12-13 00:00:00',NULL,'TEAM_C','PLAYER7';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1987-05-13 00:00:00',NULL,'TEAM_C','PLAYER8';
INSERT INTO PLAYERS (BIRTHDAY,CAPTAIN,TEAM,NAME) SELECT '1995-04-13 00:00:00',NULL,'TEAM_C','PLAYER9';
set @rownum=0;
SET @TEAM_names:='';
UPDATE Players p
JOIN(
SELECT
@rownum:=CASE WHEN @TEAM_names=TEAM THEN @rownum+1 ELSE 1 END as Row,
@TEAM_names:=TEAM AS TEAM,
name,birthday,captain
FROM Players
ORDER BY TEAM,BIRTHDAY
) ord on ord.NAME=p.name
AND ord.BIRTHDAY=p.BIRTHDAY
AND ord.TEAM=p.team
SET p.Captain= CASE WHEN Row=1 THEN true ELSE false END
SELECT *
FROM PLAYERS
ORDER BY TEAM,BIRTHDAY;
source to share