RE: Top X Scores

[Kristen Winsor]

don't know if this helps but...


I just had to do something similar and ended up using Top instead of MAX


This is how I returned the result of the claimNUmber associated to the
Top/ Max ClaimID

SELECT  TOP 1 ClaimID,
                      (SELECT ClaimNumber
                       FROM   claim Claim_ClaimNumber
                       WHERE  Claim_claimNumber.ClaimID = claim.claimID)
AS ClaimNumber
FROM     Claim
WHERE    PolicyID= #ATTRIBUTES.policyID#
ORDER BY ClaimID DESC


-----Original Message-----
From: Mike Townend [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 19, 2004 9:44 AM
To: SQL
Subject: Top X Scores

Hi,

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]

---