asked on

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.

tlovie

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

vizint

ASKER

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

tlovie

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?

vizint

ASKER

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

vizint

ASKER

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)

tlovie

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).

vizint

ASKER

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.

tlovie

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)

vizint

ASKER

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!

tlovie

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)

vizint

ASKER

For some reason, this is now resulting in some duplicate results... any ideas?

tlovie

My guess is because some questions are included on multiple quizzes from quiz_node_relationship.

vizint

ASKER

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!

tlovie

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)

vizint

ASKER

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)

tlovie

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'

vizint

ASKER

I've attached the result...

result5.jpg

tlovie

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?

vizint

ASKER

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?

ASKER CERTIFIED SOLUTION

tlovie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vizint

ASKER

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!

vizint

ASKER

tlovie helped me with this problem throughout the day - amazing help.