CODEWITHSUNDEEP
mysqlsql
abc
abc

Reputation: 11929

find max ID with the largest number of occurrence of an attribute

Making some exercises I don't know how to make this query

Having this 2 tables

StudentTable(IDstudent,....)

Exam(IDexam,...,student,...,result)

where

example

 StudentTable
 IDstudent
 S0001
 S0002
 S0003


 EXAM
 IDexam     student    result
  1          S0001      true
  2          S0002      true
  3          S0002      true
  4          S0003      false

The query have to show the ID of student with the largest number of true in exam and the number

In the case of example S0002 2

I've tried

  SELECT 
      student, count(1)
  FROM
       Exam  E join StudentTable  S on E.student=S.id_student
  WHERE result='true'
  GROUP by student

What I have is

    S0001    1
    S0002    2

but I don't know how take the max

This is the link to the schema http://sqlfiddle.com/#!2/895ea/8

Upvotes: 0

Views: 348

Answers (3)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

SELECT 
  student, count(result) AS number
FROM
   Exam  E join StudentTable  S on E.student=S.id_student
WHERE 
   result='true'
GROUP BY 
   student 
HAVING 
   number = (SELECT COUNT(result) FROM exam WHERE result='true' GROUP BY student ORDER BY 1 DESC LIMIT 1)

Link to SQL Fiddle

Upvotes: 2

John Woo
John Woo

Reputation: 263803

One thing I like this query is that it supports duplicate student having the highest number of true answer.

SELECT  a.*
FROM    StudentTable a
        INNER JOIN
        (
            SELECT  Student
            FROM    Exam
            WHERE   result = 'true'
            GROUP   BY Student
            HAVING  COUNT(*) =
                    (
                        SELECT  COUNT(*) count
                        FROM    Exam
                        WHERE   result = 'true'
                        GROUP   BY Student
                        ORDER   BY count DESC
                        LIMIT   1
                    )
        ) b ON a.IDStudent = b.Student

Upvotes: 3

Khadim Ali
Khadim Ali

Reputation: 2598

Try this:

  SELECT 
    student, count(1)
  FROM
       Exam  E join StudentTable  S on E.student=S.id_student
  WHERE result='true'
  GROUP by student 
  ORDER by 2 DESC
  LIMIT 0,1

LIMIT (N,N) clause in MySQL is equivalent to TOP (N) in T-SQL

Upvotes: 4

Related Questions