2

Print the name(s) and sid(s) of the student(s) enrolled in the most classes

Enroll

sid  class number

1     23
2     54
1     54
3     43
1     43
2     43

student sid sname

1    sagar
2    kiran
3    ravi
4    vishal

output

sid   sname

1     sagar
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Sagar Pise
  • 837
  • 10
  • 20
  • http://stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column#588708 has a lot of good applicable material to this simple question, the terms you want to look for are aggregates (`GROUP BY`, `COUNT` specifically - and you can combine `GROUP BY` with `COUNT`) and `ORDER BY` with `LIMIT 1`. – cfeduke Jul 16 '13 at 11:27

2 Answers2

6

Group enrollments by students, order by count and use limit 1:

select s.id, s.name
from student s
join enroll e on e.sid = s.id
group by s.id, s.name
order by count(*) desc
limit 1

Note how you don't need the select count(*) - you may simply refer to it.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

I think this will help you

SELECT <column_name> FROM <table_name> WHERE <column_name>=
(SELECT <column_name>
 FROM (SELECT <column_name>, count(*) as cnt FROM <table_name> GROUP BY <column_name>) AS foo
 WHERE foo.cnt=(SELECT MAX(c) FROM (SELECT <column_name>,count(*) AS c FROM <column_name> GROUP BY <column_name>) AS bar)) limit 1
Vishnu Raut
  • 36
  • 3