CODEWITHSUNDEEP
sqloracle-database
frazzled
frazzled

Reputation: 27

SQL subqueries - the highest number of courses

I'm learning SQL subqueries by myself in ORACLE. I made up two tables. What I am trying to do is to select who (by name) attends the highest number of courses.

I have two tables:

Courses:
id     course
1      1
1      2
2      1
3      1

Students:
id   name
1    John Smith
2    Mark Jones
3    Lilly Wilson

For starters I am trying to select the max by id (so id 1) but even this doesn't work. No idea how to select the name.

  SELECT x.id, MAX(x.count) FROM 
  (SELECT c.id, count(*)
  FROM courses c JOIN students s ON c.id=s.id
  GROUP BY c.id) x;

Upvotes: 0

Views: 58

Answers (2)

psaraj12
psaraj12

Reputation: 5072

To get the highest number of courses you can do the below

   with data as(
    SELECT x.id id ,x.name name, x.cnt cnt FROM 
      (SELECT c.id,c.name, count(*) cnt
      FROM courses c JOIN students s ON c.id=s.id
      GROUP BY c.id,c.name)x),
    max_courses as
              (select max(cnt) cnt from data)
    select d.id,d.name from data d inner join max_courses mx
    on d.cnt=mx.cnt

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Well, if you are learning subqueries, you should learn when they are appropriate. You can answer your question (as least in Oracle 12c+) without subqueries:

select s.name
from courses c join
     students s
     on c.id = s.id
group by s.id, s.name
order by count(*) desc
fetch first 1 row only;

Note that what you call courses.id should have an appropriate name, such as student_id.

Upvotes: 1

Related Questions