this might not actually be that complicated, but i don't know if there is a single sql statement i could use to do it.
I have the following table. What I am trying to do is to retrieve the TOP THREE drivers. Which means I want to print out on the webpage “driver name”, Season, TOTALPOINTS.
So basically, I need to add up the individual points of each driver FOR EACH ROUND to get the total points for each driver. Then I have to list the drivers based on highest points, and keep only the top three.
All this will also have to be within the same season.. (the table currently has only one season "0708".. later it will have more..)
How can it be done?
CREATE TABLE IF NOT EXISTS `points` (
`driver` tinytext NOT NULL,
`sid` tinytext NOT NULL,
`round` tinytext NOT NULL,
`place` tinyint(4) NOT NULL,
`points` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `points`
--
INSERT INTO `points` (`driver`, `sid`, `round`, `place`, `points`) VALUES
('Num One', '0708', '01', 1, 9),
('Num Two', '0708', '01', 4, 6),
('Num Three', '0708', '01', 3, 7),
('Num Four', '0708', '01', 2, 8),
('Num Five', '0708', '01', 5, 5),
('Num Six', '0708', '01', 7, 3),
('Num Seven', '0708', '01', 6, 4),
('Num One', '0708', '02', 1, 9),
('Num Seven', '0708', '02', 4, 6),
('Num Two', '0708', '02', 3, 7),
('Num Five', '0708', '02', 2, 8),
('Num Three', '0708', '02', 5, 5),
('Num Six', '0708', '02', 7, 3),
('Num Four', '0708', '02', 6, 4),
('Num Two', '0708', '03', 1, 9),
('Num Four', '0708', '03', 4, 6),
('Num One', '0708', '03', 3, 7),
('Num Three', '0708', '03', 2, 8),
('Num Seven', '0708', '03', 5, 5),
('Num Six', '0708', '03', 7, 3),
('Num Five', '0708', '03', 6, 4),
('Num One', '0708', '04', 1, 9),
('Num Two', '0708', '04', 4, 6),
('Num Four', '0708', '04', 3, 7),
('Num Three', '0708', '04', 2, 8),
('Num Five', '0708', '04', 5, 5),
('Num Seven', '0708', '04', 7, 3),
('Num Six', '0708', '04', 6, 4),
('Num Three', '0708', '05', 1, 9),
('Num Two', '0708', '05', 4, 6),
('Num One', '0708', '05', 3, 7),
('Num Four', '0708', '05', 2, 8),
('Num Five', '0708', '05', 5, 5),
('Num Six', '0708', '05', 7, 3),
('Num Seven', '0708', '05', 6, 4),
('Num Three', '0708', '06', 1, 9),
('Num One', '0708', '06', 4, 6),
('Num Two', '0708', '06', 3, 7),
('Num Six', '0708', '06', 2, 8),
('Num Four', '0708', '06', 5, 5),
('Num Seven', '0708', '06', 7, 3),
('Num Five', '0708', '06', 6, 4);
