Tricky SQL question

Tags:    databaser

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

BTW
I'm not sure the foreign key restriction is correct but I do not think it matter for this case.

Best regards
Fredrik



6 svar postet i denne tråd vises herunder
1 indlæg har modtaget i alt 3 karma
Sorter efter stemmer Sorter efter dato
What type of database is it? Anyway you could do something like

SELECT TOP 10 myfields FROM mytable WHERE mycriteria>10 ORDER BY points DESC

This query would get the top rows ordered by number of points. Maybe you could use this type of query to solve your problem.

Mvh. Thomas Lykke Petersen



Dont know if iam getting it wrong..

But cant u just add : ORDER BY `competitors.votes` DESC LIMIT 0 , 10;

Where 10 is the lenght of the list..


Med venlig hilsen
/Jokke Jensen
www.jj-multimediedesign.dk



TOP 10 is access database..

Med venlig hilsen
/Jokke Jensen
www.jj-multimediedesign.dk



TOP 10 is access database..

Med venlig hilsen
/Jokke Jensen
www.jj-multimediedesign.dk

Best regards
Fredrik

Hello!

Thanks for all help but I had to solve it with 2 separate questions. I really thougt that I should be able to combine them, but no.


SELECT competition_id, max(votes), startdate, stopdate
FROM competitors, competitions
WHERE
site_id = ?
AND
competitions.id = competition_id
AND
competitions.stopdate < ?
GROUP BY
competition_id

ORDER BY startdate DESC;



SELECT competitors.id, competitors.name, competitors.description, customers.name
FROM competitors, customers
WHERE
competition_id = ?
AND
votes = ?
AND
customers.id = competitors.customer_id
ORDER BY
competitors.name;


I use it from preparedstatements in Java, the ? is changeable parameteres.

Best regards
Fredrik

BTW it is a MySQL 3.23 And booth of you should give me "svar" so I can reward you all!



Ups...

[Redigeret d. 19/10-04 20:48:08 af Nick Frederiksen]



For MySQL you should use LIMIT instead of TOP ..

Mvh. Thomas Lykke Petersen



t