/* -------------------------------------------------------------------------- */
/* Various SELECT to obtain the "median" of a numeric column */ 
/* -------------------------------------------------------------------------- */

/* -------------------------------------------------------------------------- */
/* MYSQL - Below needs to be executed in 2 steps */
/* -------------------------------------------------------------------------- */

SELECT FLOOR(COUNT(*)/2) FROM payment;          /*Step1 - find the mid-point*/

SELECT * FROM payment               /*Step2 - select the record at mid-point*/
ORDER BY amount
LIMIT 1 OFFSET 4;              /*Substitute result of step1 for the number 4*/




/* -------------------------------------------------------------------------- */
/* ORACLE - Below needs to be executed in 2 steps */
/* -------------------------------------------------------------------------- */

SELECT FLOOR(COUNT(*)/2) FROM payment;          /*Step1 - find the mid-point*/

SELECT * FROM payment
ORDER BY amount
OFFSET 4 ROWS FETCH FIRST 1 ROW ONLY;


/* -------------------------------------------------------------------------- */
/* Either Database - All in one step 
   --------------------------------------------------------------------------
   Join the table to itself
   Select where p1.amount >= p2.amount       (gives you 1/2 the joined table)
   Group by p1.amount                        (sub-total the number of p1.amount)
   Order by p1.amount                        (rank the p1.amount ascending)
   Select amount, rank where rank = 1/2 the count  (gives you the middle record) 
 Example:
   if amounts are       = 5,6,7,8,9
   cartesian product    = 5-5,5-6,5-7,5-8,5-9  
                          6-5,6-6,6-7,6-8,6-9  
                          7-5,7-6,7-7,7-8,7-9  
                          8-5,8-6,8-7,8-8,8-9  
                          9-5,9-6,9-7,9-8,9-9
   p1.amount>=p2.amount = 5-5,  6-5,6-6,   7-5,7-6,7-7, 
                          8-5,8-6,8-7,8-8, 9-5,9-6,9-7,9-8,9-9 
   group by amount      = 5 6 7 8 9
   count(amt) as rank   = 5->1  6->2  7->3  8->4  9->5                        
   order by amount      = 5,6,7,8,9
   ceil((count(*)+1)/2) = 3
   select record with rank = 3 -> value 7      
----------------------------------------------------------------------------- */

SELECT payment_num, vendor, amount, rank 
FROM 
   (SELECT p1.payment_num, p1.vendor, p1.amount, COUNT(p1.amount) AS rank     
    FROM payment p1 JOIN payment p2 
      ON p1.amount >= p2.amount 
    GROUP BY p1.amount, p1.payment_num, p1.vendor 
    ORDER BY p1.amount) p3 
WHERE rank = (SELECT CEIL((COUNT(*)+1)/2) FROM payment)