# Find the second highest value from a subquery or derived table

I have a subquery which returns the following results.

```'c003', '120000.00'
'c002', '90000.00'
'c001', '20000.00'
'c005', '8000.00'
'c004', '5000.00'

```

I want to find the second highest value from this. How can it be done?

The subquery is :

``````SELECT SUM(coursefees)
FROM enrollment e INNER JOIN batch b INNER JOIN course c
ON e.batchid = b.batchid AND b.courseid = c.courseid
GROUP BY c.courseid;
``````

Write your join properly and just limit the result set after sorting if it's enough for you to get the second value, no matter if there are two or more courses with the same sum of coursefees. If you need to consider this, it becomes more complicated. Just let me know...

``````SELECT SUM(coursefees) AS sum_coursefees
FROM enrollment e INNER JOIN batch b ON e.batchid = b.batchid
INNER JOIN course c ON b.courseid = c.courseid
GROUP BY c.courseid
ORDER BY sum_coursefees DESC
LIMIT 1 OFFSET 1;
``````

UPDATE:

That should do it:

``````SELECT * FROM (
SELECT
c.courseid,
@rank := IF(@prev = SUM(coursefees), @rank, @rank + 1) AS rank,
@prev := SUM(coursefees) AS sum_coursefees
FROM enrollment e
INNER JOIN batch b ON e.batchid = b.batchid
INNER JOIN course c ON b.courseid = c.courseid
, (SELECT @rank:=0, @prev:=NULL) var_init
GROUP BY c.courseid
ORDER BY sum_coursefees DESC
) sq
WHERE rank = 2;
``````

Note though, that the order in the select clause is important. Do not switch columns.

Another approach to this problem would be to write a query to find the maximum value that you want to exclude, and use it in the criteria in a second query that might look something like this:

`SELECT Max(sum_coursefees) AS fee_2 FROM subquery WHERE sum_coursefees < (SELECT Max(sum_coursefees) AS fee_1 from sum_coursefees);`

where `fee_1` is the global maximum, and `fee_2` is the second highest value.

By setting the `WHERE` clause to limit values less than the 'maximum', that value will be excluded and the 2nd largest value will be returned.