I have a table of gymnastics results where only the individual apparatus scores are recorded so I'm using SUM to create an overall score. Obviously I can sort on that but I would really like to add an extra column for 'rank/position' based on the overall score value for each gymnast without having to resort to further processing in PHP. I would also like to have a 'rank/position' for each individual apparatus too! :)
Here's some example data from my 'results' table:
+---------+--------------+-------+
| gymnast | apparatus_id | score |
+---------+--------------+-------+
| Isla | 1 | 12 |
| Isla | 2 | 10.4 |
| Isla | 3 | 11.5 |
| Bethany | 1 | 11.5 |
| Bethany | 2 | 10 |
| Bethany | 3 | 11 |
| Anna | 1 | 12 |
| Anna | 2 | 13 |
| Anna | 3 | 10.25 |
+---------+--------------+-------+
I then have a MySQL query something like the below to merge the individual apparatus results into a single column for each gymnast and create the overall score:
SELECT r.gymnast, app1.score AS app1_score, app2.score AS app2_score, app3.score AS app3_score, ROUND(app1.score+app2.score+app3.score, 2) AS overall_score
FROM (results r)
JOIN apparatuses a ON a.id = r.apparatus_id
LEFT JOIN results app1 ON app1.gymnast_id = r.gymnast_id AND app1.apparatus_id=1
LEFT JOIN results app2 ON app2.gymnast_id = r.gymnast_id AND app2.apparatus_id=2
LEFT JOIN results app3 ON app3.gymnast_id = r.gymnast_id AND app3.apparatus_id=3
GROUP BY r.gymnast
ORDER BY overall_score desc
So now I'm stuck, how can I build on this query to add a rank/position for each apparatus and overall?
+---------+------------+----------+------------+----------+------------+----------+---------------+-------------+
| gymnast | app1_score | app1_pos | app2_score | app2_pos | app3_score | app3_pos | overall_score | overall_pos |
+---------+------------+----------+------------+----------+------------+----------+---------------+-------------+
| Isla | 12 | 1 | 10.4 | 2 | 11.5 | 1 | 33.9 | 2 |
| Bethany | 11.5 | 2 | 10 | 3 | 11 | 2 | 32.5 | 3 |
| Anna | 12 | 1 | 13 | 1 | 10.25 | 3 | 35.25 | 1 |
+---------+------------+----------+------------+----------+------------+----------+---------------+-------------+
There is a SQL Fiddle with some example data here (http://sqlfiddle.com/#!2/2e251e) if anyone wants to have a play. :)
Edit: The useful bit...
CREATE TABLE `apparatuses` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `apparatuses` (`id`, `name`)
VALUES
(2,'Bars'),
(3,'Beam'),
(4,'Floor'),
(5,'Range & Conditioning'),
(1,'Vault');
CREATE TABLE `gymnasts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `gymnasts` (`id`, `name`)
VALUES
(21,'Abigail English'),
(73,'Alice Brunn'),
(40,'Amelia Hornik'),
(29,'Amelie Morgan'),
(53,'Anabelle Johnny'),
(36,'Anais Saunders '),
(2,'Anna Browne'),
(56,'Anna Walters'),
(33,'Annabelle Pinhorne'),
(54,'Aurora-Leigh Howard'),
(4,'Bethany Austin'),
(79,'Betty Macland'),
(37,'Bobbie Hardy'),
(6,'Bryony-Jasmine Ludlow'),
(48,'Caitlin Donaghy'),
(23,'Charlotte Anderson-Priddle'),
(63,'Chloe Holt'),
(34,'Chloe Ramse'),
(43,'Daisy Ellis'),
(35,'Dolcie Hansler'),
(26,'Elisha Stott'),
(76,'Ella Mead'),
(78,'Ellie Kis'),
(27,'Ellie-Mae Peach'),
(67,'Emily Rodrigo'),
(69,'Emily Tait'),
(71,'Emma Violet Merchant'),
(57,'Eva Matthews'),
(41,'Florence Self'),
(16,'Freya Shirley'),
(86,'Georgia Davies'),
(77,'Georgia Dawes'),
(13,'Georgia Luff'),
(22,'Georgia Pickford'),
(14,'Hallie Parkes'),
(45,'Helen Drummie'),
(50,'Hermionie Jones'),
(60,'Holly Rider'),
(72,'Isabel Schofield'),
(1,'Isla Pitman'),
(58,'Jemima Gregory'),
(18,'Jessica Siertsema'),
(20,'Joanne Pande'),
(84,'Kathryn Brooks'),
(83,'Katie Rutherford'),
(75,'Katie Stokes'),
(59,'Kiera Bessant'),
(28,'Kiera-Lily Rumley'),
(10,'Leah Stinson'),
(68,'Lexi Grinstead'),
(46,'Lexia Mullins'),
(61,'Lili Rogers'),
(24,'Lili-Mae Snuggs'),
(82,'Lizzieanna Quigley'),
(65,'Lotie Absolom'),
(85,'Lucy Washbourn'),
(30,'Maisie Lloyd-Jones'),
(70,'Mia Fergusson'),
(25,'Mia Perryment'),
(49,'Mikah Grieg'),
(44,'Millie Kerr'),
(7,'Mya Thomas-Osbourne'),
(11,'Natalia Ivanov'),
(62,'Natalia Tennant'),
(80,'Natasha Granville'),
(87,'Nathan Pitman'),
(66,'Niamh Hilton'),
(17,'Paige Cardner'),
(39,'Pauline Gibbe'),
(19,'Phoebe Boyton'),
(51,'Phoebe Johnson'),
(38,'Pippa Nowers'),
(42,'Poppy McBride'),
(8,'Rebekah Taylor'),
(31,'Ruby Brewer'),
(52,'Ruby Savage'),
(5,'Sasha Poole'),
(9,'Scarlett Liggins'),
(74,'Shannon Laverty'),
(55,'Sienna Chantey-Jowell'),
(47,'Sienna Rutherford'),
(3,'Sophie Prior'),
(12,'Sydney Whittle'),
(81,'Vicki Johnson'),
(32,'Zara Ahddoud');
CREATE TABLE `results` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`gymnast_id` int(11) NOT NULL,
`apparatus_id` int(11) NOT NULL,
`score` decimal(4,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `gymnast_id` (`gymnast_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `results` (`id`, `gymnast_id`, `apparatus_id`, `score`)
VALUES
(5,1,1,11.55),
(6,1,2,13.15),
(7,1,3,11.40),
(8,1,4,11.80),
(9,1,5,12.30),
(10,2,1,11.75),
(11,2,2,12.65),
(12,2,3,11.45),
(13,2,4,11.30),
(14,2,5,12.55),
(26,3,1,12.20),
(27,3,2,12.65),
(28,3,3,10.85),
(29,3,4,11.70),
(30,3,5,10.40),
(31,4,1,11.00),
(32,4,2,12.25),
(33,4,3,10.75),
(34,4,4,11.35),
(35,4,5,11.90),
(36,5,1,11.80),
(37,5,2,11.50),
(38,5,3,9.80),
(39,5,4,11.60),
(40,5,5,11.65),
(41,11,1,9.00),
(42,11,2,11.10),
(43,11,3,5.90),
(44,11,4,9.70),
(45,11,5,10.30),
(46,6,1,12.75),
(47,6,2,12.90),
(48,6,3,11.15),
(49,6,4,12.15),
(50,6,5,10.70),
(51,10,1,10.60),
(52,10,2,12.50),
(53,10,3,8.35),
(54,10,4,11.30),
(55,10,5,9.80),
(56,8,1,11.10),
(57,8,2,11.05),
(58,8,3,11.90),
(59,8,4,10.45),
(60,8,5,10.70),
(61,9,1,9.50),
(62,9,2,11.85),
(63,9,3,5.75),
(64,9,4,9.70),
(65,9,5,10.75),
(66,12,1,11.85),
(67,12,2,11.65),
(68,12,3,11.65),
(69,12,4,10.90),
(70,12,5,8.80),
(71,13,1,11.50),
(72,13,2,11.80),
(73,13,3,10.40),
(74,13,4,10.10),
(75,13,5,10.35),
(76,14,1,11.40),
(77,14,2,11.10),
(78,14,3,10.80),
(79,14,4,11.05),
(80,14,5,11.25),
(81,7,1,11.70),
(82,7,2,13.00),
(83,7,3,11.30),
(84,7,4,12.55),
(85,7,5,11.75),
(86,16,1,12.30),
(87,16,2,13.30),
(88,16,3,9.30),
(89,16,4,11.85),
(90,16,5,8.25),
(91,17,1,12.50),
(92,17,2,13.05),
(93,17,3,12.00),
(94,17,4,12.30),
(95,17,5,8.15),
(96,18,2,10.20),
(97,18,3,10.90),
(98,18,4,9.95),
(99,18,5,10.25),
(100,19,1,12.80),
(101,19,2,13.55),
(102,19,3,11.45),
(103,19,4,12.30),
(104,19,5,10.70),
(105,20,1,11.90),
(106,20,2,11.35),
(107,20,3,11.85),
(108,20,4,11.55),
(109,20,5,9.05),
(110,21,1,12.65),
(111,21,2,11.00),
(112,21,3,9.80),
(113,21,4,11.95),
(114,21,5,11.70),
(115,22,1,11.40),
(116,22,2,12.65),
(117,22,3,8.80),
(118,22,4,11.10),
(119,22,5,9.75),
(120,23,1,12.00),
(121,23,2,9.35),
(122,23,3,8.30),
(123,23,4,11.60),
(124,23,5,11.85),
(125,24,1,13.00),
(126,24,2,9.40),
(127,24,3,7.30),
(128,24,4,11.40),
(129,24,5,11.85),
(130,25,1,11.60),
(131,25,2,10.20),
(132,25,3,6.00),
(133,25,4,10.55),
(134,25,5,12.10),
(135,26,1,11.40),
(136,26,2,11.20),
(137,26,3,6.70),
(138,26,4,10.25),
(139,26,5,10.70),
(140,27,1,13.55),
(141,27,2,9.10),
(142,27,3,10.70),
(143,27,4,9.30),
(144,27,5,7.35),
(145,28,1,12.65),
(146,28,2,12.50),
(147,28,3,3.90),
(148,28,4,9.35),
(149,28,5,10.40),
(150,29,1,13.20),
(151,29,2,11.90),
(152,29,3,10.90),
(153,29,4,12.65),
(154,29,5,12.90),
(155,30,1,12.30),
(156,30,2,10.00),
(157,30,3,9.80),
(158,30,4,9.70),
(159,30,5,12.50),
(453,18,1,0.00);
