Selecting Top Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting Top Rows

Author

Topic

wibo
Starting Member

2 Posts

Posted - 2007-12-03 : 07:14:05

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

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-03 : 07:27:14

You didn't give the name of the above view, but:


select 
intMemberTeamId,
max(intColumnId) as Score
from <yourviewname>
group by intMemberTeamId

wibo
Starting Member

2 Posts

Posted - 2007-12-03 : 09:19:20

Thanks RickD, works perfectly

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-03 : 09:47:18

be careful about using ORDER BY in a view. It's not guaranteed to work. Better to put the order by in the query that targets the view.

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-04 : 01:01:38

quote:
Originally posted by jezemine

be careful about using ORDER BY in a view. It's not guaranteed to work. Better to put the order by in the query that targets the view.

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

elsasoft.org

Also refer http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources