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

MySQL query to get the record based on MAX on two columns and MIN on one

Hello,

The question title appears stupid but I didn't find better words to phrase it. I've a very complex query that comes from multiple joins because this is the way Drupal database structure is.

Here is the scenario:
There are 7 Quizzes
One should attempt all the Quizzes but this is not mandatory
So we combine all the records and
1. Now the winner is who scored MAX
2. But if two or more users have the same score then
Who attempted MAX number of quizzes, e.g. all 7, would be winner whereas who has the same score but attempted 6 or 5 quizzes won't be.
3. If the score and the Max attempts are same then who took the least time would be the winner

Following is my query that gives me all the records combined and I can loop through the recordset in PHP and get the winner based on the above mentioned criteria. I was wondering if I can do the same by tweaking the query through Group By on u.mail and some ORDER BY etc.?

SELECT n.nid, n.title, n.vid, u.name, u.mail, u.uid, qnrs.result_id, qnrs.score, qnrs.time_start, qnrs.time_end, qnp.pass_rate,
      profile_values_profile_class.value AS class,
      profile_values_profile_school_name.value AS school_name,
      profile_values_profile_first_name.value AS first_name,
      profile_values_profile_last_name.value AS last_name
FROM node n
INNER JOIN term_node tn ON n.vid = tn.vid
INNER JOIN quiz_node_results qnrs ON qnrs.nid = n.nid
INNER JOIN users u ON u.uid = qnrs.uid
INNER JOIN quiz_node_properties qnp ON qnrs.vid = qnp.vid
LEFT JOIN profile_values profile_values_profile_class ON u.uid = profile_values_profile_class.uid AND profile_values_profile_class.fid = '1'
LEFT JOIN profile_values profile_values_profile_school_name ON u.uid = profile_values_profile_school_name.uid AND profile_values_profile_school_name.fid = '2'
LEFT JOIN profile_values profile_values_profile_first_name ON u.uid = profile_values_profile_first_name.uid AND profile_values_profile_first_name.fid = '7' LEFT JOIN profile_values profile_values_profile_last_name ON u.uid = profile_values_profile_last_name.uid AND profile_values_profile_last_name.fid = '8'
WHERE n.type = 'quiz' AND tn.tid = 63
ORDER BY qnrs.score DESC


Thanks
0
practitioner
Asked:
practitioner
  • 2
2 Solutions
 
pzajdelCommented:
You can order by attempts number and than by time taken.
Having table named 'attempt' with columns named respectively 'points' (number of points per attempt), 'participent' (name or id) and 'time' (in seconds) top 10 highscore SQL would look like underneath.

SELECT MAX(points) AS points, COUNT(participent) AS attempts, SUM(time) AS time
FROM  attempt
GROUP BY participent
ORDER BY points DESC, attempt, time
LIMIT 10
0
 
awking00Commented:
To determine the winner -
select * from
(select name, count(quizid), sum(score) ,sum(endtime - starttime)
 from quizzes
 group by name
 order by  sum(score) desc,count(quizid) desc,sum(endtime - starttime)) as x
limit 1;
0
 
practitionerAuthor Commented:
Thanks awking00, what you suggested worked for me with a lil change and according to my requirement, here is what I wrote:

$sql = "SELECT n.nid, n.title, n.vid, u.name, u.mail, sum(qnrs.score) total_score, count(u.mail) total_attempts, SUM(CAST(qnrs.time_end AS SIGNED) - CAST(qnrs.time_start AS SIGNED)) total_time_taken, u.uid, qnrs.result_id, qnrs.time_start, qnrs.time_end, qnp.pass_rate, 
		  profile_values_profile_class.value AS class, 
		  profile_values_profile_school_name.value AS school_name, 
		  profile_values_profile_first_name.value AS first_name, 
		  profile_values_profile_last_name.value AS last_name 
		  FROM {node} n 
		  INNER JOIN {term_node} tn ON n.vid = tn.vid 
		  INNER JOIN {quiz_node_results} qnrs ON qnrs.nid = n.nid 
		  INNER JOIN {users} u ON u.uid = qnrs.uid 
		  INNER JOIN {quiz_node_properties} qnp ON qnrs.vid = qnp.vid 
		  LEFT JOIN {profile_values} profile_values_profile_class ON u.uid = profile_values_profile_class.uid AND profile_values_profile_class.fid = '1' 
		  LEFT JOIN {profile_values} profile_values_profile_school_name ON u.uid = profile_values_profile_school_name.uid AND profile_values_profile_school_name.fid = '2'
		  LEFT JOIN {profile_values} profile_values_profile_first_name ON u.uid = profile_values_profile_first_name.uid AND profile_values_profile_first_name.fid = '7' LEFT JOIN profile_values profile_values_profile_last_name ON u.uid = profile_values_profile_last_name.uid AND profile_values_profile_last_name.fid = '8' 
		  WHERE n.type = 'quiz'";
  if ($tid) {
    $sql .= " AND tn.tid = %d";
    $args[] = $tid;
  }
  if ($uid != 0) {
    $sql .= " AND qnrs.uid = %d";
    $args[] = $uid;
  }
  $sql .= " GROUP BY u.mail";
  $sql .= " ORDER BY total_score DESC, total_attempts DESC, total_time_taken";

Open in new window

0
 
practitionerAuthor Commented:
Thanks awking00 and pjazdel for the suggestions.

I awarded full points to awking00 but chose my comment as the best answer only because it is Drupalized and if someone looking for a similar solution can refer to it. The actual suggestion was by awking00 only.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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