• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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
0
vizint
Asked:
vizint
  • 12
  • 10
1 Solution
 
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
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
tlovieCommented:
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now