Show only rows with highest value in a column?

I'm trying to refine a mysql query so that it will return rows with the highest values in the "Version ID" column.

Here is the query:
SELECT quiz_node_relationship.parent_nid,
      quiz_multichoice_answers.answer,
      quiz_multichoice_answers.question_nid,
      MAX(quiz_multichoice_answers.question_vid) AS MAX_question_vid
FROM quiz_node_relationship RIGHT OUTER JOIN quiz_multichoice_answers ON quiz_node_relationship.child_nid = quiz_multichoice_answers.question_nid

WHERE quiz_node_relationship.parent_nid = '23'

GROUP BY quiz_node_relationship.parent_nid, quiz_multichoice_answers.answer, quiz_multichoice_answers.question_nid

ORDER BY quiz_multichoice_answers.question_nid ASC, MAX_question_vid DESC

I'd like to have the table show only the records with the highest question_vid for a given question_nid.

I don't understand why the MAX function doesn't achieve this here, but my mySQL chops are limited... thanks for the help.

I've attached an image of the resulting table.

 query result
vizintAsked:
Who is Participating?
 
tlovieConnect With a Mentor Commented:
SELECT qma.question_nid,
      qma.answer,
        qma.question_vid,
      qma.feedback_if_chosen,
      qma.score_if_chosen,
      qnr.parent_nid
FROM quiz_multichoice_answers qma INNER JOIN quiz_node_relationship qnr ON qma.question_nid = qnr.child_nid and qma.question_vid = qnr.child_vid
WHERE quiz_node_relationship.parent_nid = '10'
and exists (select 1 from
(SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) as qmax where qma.question_nid=qmax.question_nid and qma.question_vid=qmax.max_question_vid)
and exists (select 1 from
(SELECT parent_nid, max(parent_vid) as max_parent_vid FROM quiz_node_relationship GROUP BY
parent_nid) as pmax where qnr.parent_nid=pmax.parent_nid and qnr.parent_vid=max_parent_vid)
0
 
tlovieCommented:
There are several ways to write this query, this might work for you:  

SELECT quiz_node_relationship.parent_nid,
      qma.answer,
      qma.question_nid,
      qma.question_vid
FROM quiz_node_relationship RIGHT OUTER JOIN quiz_multichoice_answers as qma
INNER JOIN (SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) as qmax on qmax.question_nid=qma.question_nid and qmax.max_question_vid=qma.question_vid
ON quiz_node_relationship.child_nid = qqma.question_nid

WHERE quiz_node_relationship.parent_nid = '23'

ORDER BY qma.question_nid ASC
0
 
vizintAuthor Commented:

Thanks for your help tlovie

I've incorporated the update you sent (pretty sure I interpreted it correctly):

SELECT quiz_node_relationship.parent_nid,
      	quiz_multichoice_answers.answer,
      	quiz_multichoice_answers.question_nid,
     	quiz_multichoice_answers.question_vid

FROM quiz_node_relationship RIGHT OUTER JOIN quiz_multichoice_answers as quiz_multichoice_answers
INNER JOIN (SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) 
AS qmax ON qmax.question_nid=quiz_multichoice_answers.question_nid AND qmax.max_question_vid=quiz_multichoice_answers.question_vid
ON quiz_node_relationship.child_nid = quiz_multichoice_answers.question_nid

WHERE quiz_node_relationship.parent_nid = '23'

ORDER BY quiz_multichoice_answers.question_nid ASC

Open in new window

and the result of the query actually has more results (see attached image) - did I miss something?

Thanks again tlovie!


result2.jpg
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
tlovieCommented:
So, I guess I don't quite understand they database schema in this case.  I was assuming that quiz_multichoice_answers.question_nid combined with quiz_multichoice_answers.question_vid
forms a unique key on that table, and the selection you wanted, was the quiz_multichoice_answers.question_nid max( quiz_multichoice_answers.question_vid ).  

What happens when you do this:
SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid
does this give the question_nid, question_vid that you want?
0
 
vizintAuthor Commented:
Thanks again for your reply - It does return the proper  question_nid, question_vid

Now how do I join that back to the table so I can get other fields from the table? Is this a subquery?

As soon as I add another field - (in this case quiz_multichoice_answers.answer)

SELECT quiz_multichoice_answers.question_nid, 
	max(quiz_multichoice_answers.question_vid) AS max_question_vid, 
	quiz_multichoice_answers.answer
FROM quiz_multichoice_answers
GROUP BY quiz_multichoice_answers.question_nid, quiz_multichoice_answers.answer

Open in new window


I lose the MAX max_question_vid filter...
0
 
vizintAuthor Commented:
Okay - I finally got off my butt and learned more about subqueries

I think this what I was looking for:

SELECT question_nid,
      answer,
      question_vid,
      feedback_if_chosen,
      score_if_chosen
FROM quiz_multichoice_answers

WHERE question_vid = (SELECT max(question_vid) FROM quiz_multichoice_answers)
0
 
tlovieCommented:
yes, what I did was join a subquery back to your query to get the appropriate results.  What you have above is only the max(question_vid).... I thought that every quiz_multichoice_answers.question_nid would have it's own MAX(question_vid).
0
 
vizintAuthor Commented:
I spoke too soon - you are correct, it only returns the max(question_vid)

Using the simplified example, from my previous post, how do I JOIN rather than WHERE to list all records where the question_vid is the MAX. Do I need to do a LEFT or RIGHT JOIN?

Thanks for all of your help - I'm trying to learn how  subquery works.
 
0
 
tlovieCommented:
Lets try this:

SELECT question_nid,
      answer,
      question_vid,
      feedback_if_chosen,
      score_if_chosen
FROM quiz_multichoice_answers as qma
where exists (select 1 from
(SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) as qmax where qma.question_nid=qmax.question_nid and qma.question_vid=qmax.max_question_vid)
0
 
vizintAuthor Commented:
Brilliant!

One final thing - how do I get it to join with another table to derive the parent_nid from quiz_node_relationship.parent_nid:
SELECT question_nid, 
	answer, 
	feedback_if_chosen, 
	score_if_chosen, 
	quiz_node_relationship.parent_nid
FROM quiz_multichoice_answers AS qma INNER JOIN quiz_node_relationship ON question_nid = quiz_node_relationship.child_nid

Open in new window

I just want to be able to filter the result by quiz, so that only the answers for a specific quiz are shown.

Thanks for all your help!
0
 
tlovieCommented:
SELECT question_nid,
      answer,
      question_vid,
      feedback_if_chosen,
      score_if_chosen,
      quiz_node_relationship.parent_nid
FROM quiz_multichoice_answers as qma INNER JOIN quiz_node_relationship ON qma.question_nid = quiz_node_relationship.child_nid
where exists (select 1 from
(SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) as qmax where qma.question_nid=qmax.question_nid and qma.question_vid=qmax.max_question_vid)
0
 
vizintAuthor Commented:
For some reason, this is now resulting in some duplicate results... any ideas?
0
 
tlovieCommented:
My guess is because some questions are included on multiple quizzes from quiz_node_relationship.
0
 
vizintAuthor Commented:
Here is the query filtered by quiz_node_relationship.parent_nid:

SELECT qma.question_nid, 
	qma.answer, 
	MAX(qma.question_vid) AS MAX_question_vid, 
	qma.feedback_if_chosen, 
	qma.score_if_chosen, 
	quiz_node_relationship.parent_nid
FROM quiz_multichoice_answers qma INNER JOIN quiz_node_relationship ON qma.question_nid = quiz_node_relationship.child_nid
WHERE quiz_node_relationship.parent_nid = '10'
GROUP BY qma.question_nid, qma.answer, qma.feedback_if_chosen, qma.score_if_chosen, quiz_node_relationship.parent_nid

Open in new window


I've attached the resulting query.

This is exactly what I am looking for except I need just the MAX value of the MAX_question_vid column.

You deserve a 1000 pts!


 result of query
0
 
tlovieCommented:
I don't think you can do it that way...

Try it like this:


SELECT qma.question_nid,
      qma.answer,
        qma.question_vid,
      qma.feedback_if_chosen,
      qma.score_if_chosen,
      quiz_node_relationship.parent_nid
FROM quiz_multichoice_answers qma INNER JOIN quiz_node_relationship ON qma.question_nid = quiz_node_relationship.child_nid
WHERE quiz_node_relationship.parent_nid = '10'
and exists (select 1 from
(SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) as qmax where qma.question_nid=qmax.question_nid and qma.question_vid=qmax.max_question_vid)
0
 
vizintAuthor Commented:
Wow we are close... now I get what appears to be the exact result twice... is it OK to use the DISTINCT after the SELECT function to eliminate the redundancy?

SELECT DISTINCT qma.question_nid,
      qma.answer,
        qma.question_vid,
      qma.feedback_if_chosen,
      qma.score_if_chosen,
      quiz_node_relationship.parent_nid
FROM quiz_multichoice_answers qma INNER JOIN quiz_node_relationship ON qma.question_nid = quiz_node_relationship.child_nid
WHERE quiz_node_relationship.parent_nid = '10'
and exists (select 1 from
(SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) as qmax where qma.question_nid=qmax.question_nid and qma.question_vid=qmax.max_question_vid)
 results
0
 
tlovieCommented:
it's okay to do that, but I'd investigate the root cause of it more before doing that.....

what do you get from this:

select * from quiz_node_relationship
where quiz_node_relationship.parent_nid = '10'
and quiz_node_relationship.child_nid='12'
0
 
vizintAuthor Commented:
I've attached the result...

result5.jpg
0
 
tlovieCommented:
I think we should do this in that case:

SELECT qma.question_nid,
      qma.answer,
        qma.question_vid,
      qma.feedback_if_chosen,
      qma.score_if_chosen,
      quiz_node_relationship.parent_nid
FROM quiz_multichoice_answers qma INNER JOIN quiz_node_relationship ON qma.question_nid = quiz_node_relationship.child_nid and qma.question_vid = quiz_node_relationship.child_vid
WHERE quiz_node_relationship.parent_nid = '10'
and exists (select 1 from
(SELECT question_nid, max(question_vid) as max_question_vid FROM quiz_multichoice_answers GROUP BY question_nid) as qmax where qma.question_nid=qmax.question_nid and qma.question_vid=qmax.max_question_vid)

but looking at the results, it also looks like the parent can have multiple versions.... should we not also restrict on that?
0
 
vizintAuthor Commented:
you are correct and very thourough - that is the highest level in this hierarchy of Quiz->Question->Answer

How do we add another subquery checking on the MAX of the parent_vid?

0
 
vizintAuthor Commented:
I changed
WHERE quiz_node_relationship.parent_nid = '10'
WHERE qnr.parent_nid = '10'

and it works great...

You are a JEDI and a GENTLEMAN... extremely helpful! Thank you very much!
0
 
vizintAuthor Commented:
tlovie helped me with this problem throughout the day - amazing help.
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.