Solved

sql select max problem

Posted on 2013-01-23
6
390 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

861 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

23 Experts available now in Live!

Get 1:1 Help Now