Returning a record with a max value, need help

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

Transact-SQL (2000)

Returning a record with a max value, need help

Author

Topic

LambrettaStarr
Starting Member

5 Posts

Posted - 2004-08-09 : 10:32:49

Ok, this is going to seem basic to you all, but I am new.

I have a temp table that I am inserting records into of test scores. A user can have multiple scores for a test, since they can take it multiple times. The query I want to write would give me all of the tests they have taken, but of tests where there are duplicates, I only want the one with the highest score.

I know how to use MAX with the first field in the query, but how do I use it in a non-first field.

here is the query:

insert into @testHistory(
responseID,
userID,
SurveyID,
dateStarted,
dateCompleted,
completed,
points,
CategoryID)
select
sr.responseID,
sr.UserID,
tc.SurveyID,
sr.dateStarted,
sr.dateCompleted,
sr.completed,
sr.points,
tc.CategoryID
from usd_response as sr
inner join usd_SurveyUser as su on sr.UserID = su.userID
inner join tbl_testCenter as tc on sr.surveyID = tc.surveyID
where su.username = @studentID and sr.surveyID in (select surveyID from tbl_testCenter)

Any help would be appreciated.

Right now, this query returns all the tests, regardless of score. I want it to return one test only for each (surveyID) - I need the one with the highest amount of points.

Thanks!

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-09 : 12:53:53

So long as I understand what you want, this should do it:

select
sr.responseID,
sr.UserID,
tc.SurveyID,
sr.dateStarted,
sr.dateCompleted,
sr.completed,
max(sr.points) as points,
tc.CategoryID
from usd_response as sr
inner join usd_SurveyUser as su on sr.UserID = su.userID
inner join tbl_testCenter as tc on sr.surveyID = tc.surveyID
where su.username = @studentID and sr.surveyID in (select surveyID from tbl_testCenter)
GROUP BY sr.responseID, sr.UserID, tc.SurveyID, sr.dateStarted, sr.dateCompleted, sr.completed, tc.CategoryID

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-09 : 19:18:53

Post all table structures some data and expected results.
Manoj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources