Solved

sql select max problem

Posted on 2013-01-23
6
389 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 142

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now