I've got the following query I use for a front end system that works fine:
SELECT TOP 5 MAX(Score) AS Score, Visitor.VI_VisitorID AS ID
FROM Scores, Visitor
WHERE Scores.VI_VisitorID = Visitor.VI_VisitorID
AND Scores.ScoreDateTime BETWEEN {d '2004-05-10'} AND {ts '2004-05-30 23:59:59'}
AND Scores.ScoreActive = 1
GROUP BY Visitor.VI_VisitorID
ORDER BY Score DESC
Which gets me the top 5 scores between a certain date.
However for the backend system I want a clickable link to that score, I cant seem to adapt the query in the write way to add the score ID output, if I change the query to
SELECT TOP 5 MAX(Score) AS Score, Visitor.VI_VisitorID AS ID, ScoreID
FROM Scores, Visitor
WHERE Scores.VI_VisitorID = Visitor.VI_VisitorID
AND Scores.ScoreDateTime BETWEEN {d '2004-05-10'} AND {ts '2004-05-30 23:59:59'}
AND Scores.ScoreActive = 1
GROUP BY Visitor.VI_VisitorID, ScoreID
ORDER BY Score DESC
Then I get more records than I need... How can I adapt the query to get the top x scores and include the scoreid?
Thanks
Mikey
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
