Solved

sql select max problem

Posted on 2013-01-23
6
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 6

Accepted Solution

by:
deiaccord earned 500 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

630 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