Tricky SQL question (hope this is right forum)
Hello!I'm trying to get a result from 2 tables in a mysql database. The result will be a list of "hall of fame".
The tables looks like:
CREATE TABLE competitions
(
id INT NOT NULL AUTO_INCREMENT,
site_id INT NOT NULL,
startdate DATE NOT NULL,
stopdate DATE NOT NULL,
numberofcompetitors INT NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE competitors
(
id INT NOT NULL AUTO_INCREMENT,
competition_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(255) NOT NULL,
url VARCHAR(100),
image BLOB NOT NULL,
extension VARCHAR(5) NOT NULL,
votes INT NOT NULL,
customer_id INT,
PRIMARY KEY(id),
FOREIGN KEY (competition_id) REFERENCES competitions(id)
);
What i need to get hold of is:
For one certain site_id get the competitors (the winners) with highest votes where the competition they are assigned to has expired, in other words where stopdate is < then todays date.
I have tried this:
select competitions.id, competitors.name, competitors.votes
from competitions, competitors
where
competitions.id = competitors.competition_id
and
competitions.site_id = 3
and
competitions.stopdate < '2004-10-20';
But how do I restrict it to just get the winners like votes = max(votes).
In some competitions it may also be a shared first place where more than one competitor may have the same highest votes like some other. Then I would like to get hold of booth of the winners for that competition.
So if any one could help me with this it would be great.
So the list could look like this:
Competition id Name Votes
1 a 10
2 b 30
2 d 30
3 f 15
Best regards
Fredrik
BTW
I'm not sure the foreign key restriction is correct but I do not think it matter for this case.