sql select max problem

Hi,

I have a table which records each attempt a user has made at a quiz.
I want to select all the records for users with the maximum attempt number.

Would be grateful for any help with the code below.

Thanks

SELECT     tbl_M05_PublishedAssessments.PublishedAssessmentID, tbl_M05_PublishedAssessments.PublishedCourseId, tbl_M05_PublishedAssessments.AssessmentName,
                      tbl_M05_PublishedAssessments.PubVersion, tbl_M02_AssessmentCandidateScore.PublishedAssessmentScore, tbl_M02_AssessmentCandidateScore.AttemptNo,
                      tbl_M02_AssessmentCandidateScore.AttemptDate, tbl_M02_AssessmentCandidateScore.CandidateId
FROM         tbl_M05_PublishedAssessments INNER JOIN
                      tbl_M02_AssessmentCandidateScore ON tbl_M05_PublishedAssessments.PublishedAssessmentID = tbl_M02_AssessmentCandidateScore.PublishedAssessmentId
LVL 1
SolugaAsked:
Who is Participating?
 
deiaccordCommented:
I believe the below query should get what you want (assuming you want the most attempted assessment for each user, your reqirement is a touch vague)

SELECT     tbl_M05_PublishedAssessments.PublishedAssessmentID
	,tbl_M05_PublishedAssessments.PublishedCourseId
	,tbl_M05_PublishedAssessments.AssessmentName
	,tbl_M05_PublishedAssessments.PubVersion
	,tbl_M02_AssessmentCandidateScore.PublishedAssessmentScore
	,tbl_M02_AssessmentCandidateScore.AttemptNo
	,tbl_M02_AssessmentCandidateScore.AttemptDate
	,tbl_M02_AssessmentCandidateScore.CandidateId
FROM tbl_M05_PublishedAssessments 
INNER JOIN tbl_M02_AssessmentCandidateScore 
ON tbl_M05_PublishedAssessments.PublishedAssessmentID = tbl_M02_AssessmentCandidateScore.PublishedAssessmentId 
WHERE tbl_M05_PublishedAssessments.PublishedAssessmentID IN
	--List of AssessmentID's with the highest AtemptNo score for each CandidateId
	(SELECT m5_1.PublishedAssessmentID
	FROM tbl_M05_PublishedAssessments as m5_1
	INNER JOIN tbl_M02_AssessmentCandidateScore as m2_1
	ON m5_1.PublishedAssessmentID = m2_1.PublishedAssessmentId 
	WHERE m2_1.AttemptNo = 
		(SELECT MAX(m2_2.AttemptNo)
		FROM tbl_M02_AssessmentCandidateScore.PublishedAssessmentId m2_2
		WHERE m2_2.CandidateId  = m2_1.CandidateId
		)
	)

Open in new window

0
 
Barry CunneyCommented:
SELECT     tbl_M05_PublishedAssessments.PublishedAssessmentID, tbl_M05_PublishedAssessments.PublishedCourseId, tbl_M05_PublishedAssessments.AssessmentName,
                      tbl_M05_PublishedAssessments.PubVersion, tbl_M02_AssessmentCandidateScore.PublishedAssessmentScore,tbl_M02_AssessmentCandidateScore.AttemptNo ,
                      tbl_M02_AssessmentCandidateScore.AttemptDate, tbl_M02_AssessmentCandidateScore.CandidateId
FROM         tbl_M05_PublishedAssessments INNER JOIN
                      tbl_M02_AssessmentCandidateScore ON tbl_M05_PublishedAssessments.PublishedAssessmentID = tbl_M02_AssessmentCandidateScore.PublishedAssessmentId
JOIN
(
SELECT MAX(AttemptNo) [MaxAttempts]
FROM         tbl_M05_PublishedAssessments INNER JOIN
                      tbl_M02_AssessmentCandidateScore ON tbl_M05_PublishedAssessments.PublishedAssessmentID = tbl_M02_AssessmentCandidateScore.PublishedAssessmentId
)  Max
ON Max.[MaxAttempts]
 = tbl_M02_AssessmentCandidateScore.AttemptNo
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SolugaAuthor Commented:
Hi BCUNNEY,

That just selects the top single record out all the records.

Think I might use a temp table and build it up from there.
0
 
RehanYousafCommented:
Can you post some sample data for your tables in excel fomat

Also your desired result

will make the job much easier :-)
0
 
SolugaAuthor Commented:
Great thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.