Get other columns(attribute) with max values

Question

my dataframe

ID      COURSE_ID SEC_ID SEMESTER YEAR  GRADE
00128   CS-101    1      Fall   2009    A
00128   CS-347    1      Fall   2009    A-
12345   CS-101    1      Fall   2009    C
....

I want to get course_id & sec_id with the maximum number of registrants(count(id)) at 2009 fall.

so, I tried

select course_id, sec_id, enrollment
from (select course_id, sec_id, count(ID) as enrollment
    from takes
    where semester = 'Fall' and year = 2009
    group by course_id, sec_id)

However, this will result in each class having registrants. I want to show only those classes with the maximum number of registrants. I think I need to use max, but now I need to solve it with subsection of this code, from.(from subquery)

++ Can I solve it using the having clause? I would appreciate it if you could let me know if you can.

thank you for reading.

Make it easy to assist you: [mcve]. – jarlh Apr 22 '20 at 15:11 — jarlh, Apr 22 '20 at 15:11
What if two have the same value? – Gordon Linoff Apr 22 '20 at 15:18 — Gordon Linoff, Apr 22 '20 at 15:18
@GordonLinoff In this dataset, it is not duplicated! – beenz Apr 22 '20 at 15:38 — beenz, Apr 22 '20 at 15:38

GMB · Accepted Answer · 2020-04-22T15:54:47.800

You can filter on courses from Fall 2009, aggregate by course_id and sec_id, sort the results by their number of rows per group, and use a row-limiting clause to get the most attented course:

select course_id, sec_id, count(*) no_registrants
from takes
where semester = 'Fall' and year = '2009'
group by course_id, sec_id
order by no_registrants desc
fetch first 1 rows with ties

This allows top ties, if any. If you want just one row, you can change fetch first 1 rows with ties to fetch first 1 rows only. You might also want to add a second sort criteria to make the results deterministic (otherwise, it is undefined wich course will appear in there are ties).

In Oracle < 12c, where the row limiting clause is not available, you can use rank() instead (or row_number() to disallow ties):

select course_id, sec_id, no_registrants
from (
    select 
        course_id, 
        sec_id, 
        count(*) no_registrants,
        rank() over(order by count(*) desc) rn
    from takes
    where semester = 'Fall' and year = '2009'
    group by course_id, sec_id
) t
where rn = 1

score 0 · Answer 2 · answered Apr 22 '20 at 15:41

0

As per my understanding for your requirement, your query need just modification to get the proper result.

To get only one record with the highest number of registrants. Use your same query just add the below mentioned changes:

select course_id, sec_id, max(enrollment) as registrants
from (select course_id, sec_id, count(ID) as enrollment
from takes
where semester = 'Fall' and year = 2009
group by course_id, sec_id) as Rdet group by course_id,sec_id;

This will give you the proper result based on you requirement.

answered Apr 22 '20 at 15:41

Data_Enginner_Suraj

76
4

This will work with sql server. Are you using which server? And which version? – Data_Enginner_Suraj Apr 22 '20 at 16:03
`Are you using which server` - the question is tagged [Oracle](https://stackoverflow.com/tags/oracle/info). – GSerg Apr 23 '20 at 06:51

score 0 · Answer 3 · answered Apr 22 '20 at 15:58

0

You can place your subquery in a CTE to reuse it:

with
x as (
  select course_id, sec_id, count(*) as enrollment
  from takes
  where semester = 'Fall' and year = 2009
  group by course_id, sec_id
)
select *
from x
where enrollment = (select max(enrollment) from x)

answered Apr 22 '20 at 15:58

The Impaler

45,731
9
39
76

yes, I did it using 'with',, It was a problem solved using the from clause. – beenz Apr 22 '20 at 16:01

Get other columns(attribute) with max values

3 Answers3