How to extract second highest row from a table

Question

I have four tables :

1. Batch(batch_id, batch_start_date, batch_strength, course_id)
2. Course(course_id, course_name, course_category, course_fees, course_duration)
3. Enrollment(batch_id, student_id, enrollment_date)
4. Student(student_id, student_name, student_mail, student_date_of_birth, student_city, student_phone, student_qualification)

Now according to the question I have to display the coursename which has 2nd highest revenue collection

According to the above question here is my solution

select c.course_name, c.course_fees*count(c.course_name) Total_Revenue
from course c join(batch b join enrollment e 
on b.batch_id=e.batch_id) 
on c.course_id=b.course_id
group by c.course_name, c.course_fees
order by Total_Revenue desc ;

Now the problem is I am not able to extract the second row from the resulting table from the above code. How can I extract second row from the above resulting table?(Answer specific to Oracle 11g is appreciated)

Answer 1

Use row_number():

select *
from (select c.course_name, c.course_fees*count(c.course_name) as Total_Revenue,
             row_number() over (order by c.course_fees*count(c.course_name)) as seqnum
      from batch b join
           enrollment e 
           on b.batch_id = e.batch_id join
           course c
           on c.course_id=b.course_id
      group by c.course_name, c.course_fees
     ) bec
where seqnum = 2;

If you could have ties for first and still want the second place, then use dense_rank() instead of row_number().

Answer 2

Non-analytic solution just for the fun of it:

with r as (
    select min(c.course_name) as course_name, min(c.course_fees) * count(*) as revenue
    from
        course c
        inner join batch b on b.course_id = c.course_id
        inner join enrollment e on e.batch_id = b.batch_id
    group by c.course_id
)
select course_name, revenue
from r
where revenue = (select max(revenue) from r where revenue < (select max(revenue) from r))

This handles ties (in both first and second place.) I'm also going out on a limb and assuming that you really intended to group by course_id. This looks more like a class exercise so I don't expect there's any complication like historical fee information or anything like that going on.

EDIT Based on your comment below it sounds like you may have multiple courses with the same name and different fees. I suspect your original query will not work correctly since you are also grouping on course_fees.

Notice the change below uses a grouping on course_name and a summation on course_fees:

with r as (
    select course_name, sum(c.course_fees) as revenue
    from
        course c
        inner join batch b on b.course_id = c.course_id
        inner join enrollment e on e.batch_id = b.batch_id
    group by c.course_name
)
select course_name, revenue
from r
where revenue = (select max(revenue) from r where revenue < (select max(revenue) from r))

Answer 3

You can also create you own aggregate function. The Oracle docs mention function SecondMax as an example. But if there is a proper index, then row_numer function might give better execution plan.