3

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;

2 Answers 2

1

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.

0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.