CODEWITHSUNDEEP
mysqlmax
terry
terry

Reputation: 143

Second max based on Category in SQL

I am trying to find the second max based on two different categories. I can use analtycal function or logic to get this. I have been trying to find this through a logic.
My question is I am trying to fetch the records of second most taken exam per country by unique students.

T1
    Exam_ID    Student_ID
    123        553
    123        457
    345        563
    567        765
    678        543
    678        543
    987        123
    678        123

T2
Exam_ID Exam_name Country_name
123     SAT       USA
345     CAT       USA
567     GRE       USA
678     TOEFL     UK
987     IELTS     UK
222     CBAP      UK

This is what I tried so far,

    select  count(distinct  T1.Student_ID) count_user, 
    t2.Country_name,t2.Exam_name
    from T1
    join T2
    on T1.Exam_ID = T2.Exam_ID
    group by t2.Exam_name, t2.Country_name

By doing this I am able to get the unique student count based on each exam and country.

How can I get the second max no of exams taken by unique students based on the country?

Upvotes: 1

Views: 38

Answers (1)

GMc
GMc

Reputation: 1774

I'm not sure I fully understand what you mean by your question. Could you post the expected result along with what you are getting now?

In the mean time, I'm taking a guess that exam_id 678 in the UK (with 3 students) is the top result and 987 in the UK is the "second top result"???

If so, Row_number () might work for you. Bear in mind that row_number is usually an expensive operation in relational databases as it involves a redistribution and a sort. A similar function Rank () may be better for you depending upon how you want to handle ties. The syntax is similar, you could try both.

Try modifying your query as follows:

select count(distinct T1.student_id) count_user, Country_name, Exam_name, row_number () over (partition by country_name order by count_user desc) as row_num ...

If that gives you the numbering you want, you can then restrict the output using the qualify clause i.e. qualify row_num = 2

You may need to wrap the whole thing in a derived table as follows:

select count_user, country_name, exam_name,
row_number () over (partition by country_name order by count_user desc) as row_num     
from (
  select  count(distinct  T1.Student_ID) count_user, 
    t2.Country_name,t2.Exam_name,
    from T1 join T2
        on T1.Exam_ID = T2.Exam_ID
    group by t2.Exam_name, t2.Country_name
) detail_recs
qualify row_num = 2

Upvotes: 1

Related Questions