Solved

Show only rows with highest value in a column?

Posted on 2011-03-02
22
550 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
  • 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

18 Experts available now in Live!

Get 1:1 Help Now