Returning Top Rows

[willB]

Hello, I have a view that is returning a list of user ids and a score connected to them (its possible for more than one score to be connected). I want to select the top score for each user. I cant work out how to return this top score for each user without having to loop over the database, checking for the next user with every loop which would be very inefficient. Heres the code am using that gives me all the results back:

SELECT TOP 100 PERCENT dbo.tblMemberPrediction_Score.intMemberTeamId, dbo.tblPredVsAct.intPoints, dbo.tblMemberPrediction_Score.intColumnId,
dbo.tblMemberWeek_ColumnLetter_Cost.intMW_CL_CostId
FROM dbo.tblMemberWeek INNER JOIN
dbo.tblMemberPrediction_Score INNER JOIN
dbo.tblPred ON dbo.tblMemberPrediction_Score.intPredId = dbo.tblPred.intPredId ON
dbo.tblMemberWeek.intMemWeekId = dbo.tblMemberPrediction_Score.intMemWeekId INNER JOIN
dbo.tblPredVsAct ON dbo.tblPred.intPredId = dbo.tblPredVsAct.intPredId INNER JOIN
dbo.tblFixture_Result INNER JOIN
dbo.tblAct ON dbo.tblFixture_Result.intActId = dbo.tblAct.intActId ON dbo.tblPredVsAct.intActId = dbo.tblAct.intActId AND
dbo.tblMemberPrediction_Score.intFixtureId = dbo.tblFixture_Result.intFixtureId AND dbo.tblPred.intPredId <> dbo.tblAct.intActId INNER JOIN
dbo.tblMemberWeek_ColumnLetter_Cost ON dbo.tblMemberWeek.intMemWeekId = dbo.tblMemberWeek_ColumnLetter_Cost.intMemberWeekId
WHERE (dbo.tblMemberPrediction_Score.intSubmittedTF = 1)
GROUP BY dbo.tblMemberPrediction_Score.intMemberTeamId, dbo.tblPredVsAct.intPoints, dbo.tblMemberPrediction_Score.intColumnId,
dbo.tblMemberWeek_ColumnLetter_Cost.intMW_CL_CostId
ORDER BY dbo.tblPredVsAct.intPoints DESC

Thanks for taking the time to read this, hope it made sense ;)

will

[hemma]

Maybe it's my old eyes, but I'm having a tough time picking through your effort.

Maybe just type out the table names and columns you have for each table along with a brief example of desired output?