Solved

Show only rows with highest value in a column?

Posted on 2011-03-02
22
551 Views
Last Modified: 2012-05-11
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
Comment
Question by:vizint
[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
  • 12
  • 10
22 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 35019345
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
 

Author Comment

by:vizint
ID: 35019701

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
 
LVL 7

Expert Comment

by:tlovie
ID: 35020459
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:vizint
ID: 35029642
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
 

Author Comment

by:vizint
ID: 35029908
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
 
LVL 7

Expert Comment

by:tlovie
ID: 35030107
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
 

Author Comment

by:vizint
ID: 35030342
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
 
LVL 7

Expert Comment

by:tlovie
ID: 35030455
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
 

Author Comment

by:vizint
ID: 35030727
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
 
LVL 7

Expert Comment

by:tlovie
ID: 35030873
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
 

Author Comment

by:vizint
ID: 35030947
For some reason, this is now resulting in some duplicate results... any ideas?
0
 
LVL 7

Expert Comment

by:tlovie
ID: 35031000
My guess is because some questions are included on multiple quizzes from quiz_node_relationship.
0
 

Author Comment

by:vizint
ID: 35031208
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
 
LVL 7

Expert Comment

by:tlovie
ID: 35031501
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
 

Author Comment

by:vizint
ID: 35031684
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
 
LVL 7

Expert Comment

by:tlovie
ID: 35031804
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
 

Author Comment

by:vizint
ID: 35031827
I've attached the result...

result5.jpg
0
 
LVL 7

Expert Comment

by:tlovie
ID: 35031871
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
 

Author Comment

by:vizint
ID: 35031902
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
 
LVL 7

Accepted Solution

by:
tlovie earned 500 total points
ID: 35031932
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
 

Author Comment

by:vizint
ID: 35032096
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
 

Author Closing Comment

by:vizint
ID: 35032169
tlovie helped me with this problem throughout the day - amazing help.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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