Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql select max problem

Posted on 2013-01-23
6
Medium Priority
?
399 Views
Last Modified: 2013-01-23
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
0
Comment
Question by:Soluga
6 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38809619
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38809646
0
 
LVL 1

Author Comment

by:Soluga
ID: 38809675
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Accepted Solution

by:
deiaccord earned 2000 total points
ID: 38809891
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38810137
Can you post some sample data for your tables in excel fomat

Also your desired result

will make the job much easier :-)
0
 
LVL 1

Author Closing Comment

by:Soluga
ID: 38810167
Great thanks.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question