maximum differenence between two column

Question

there is need to compare two column value find and want to find out difference between this two column value and at the end , want to display all the values present in that table where we find out maximum difference

companyname     open    close   date
A                 10      9.8   2015-01-01
A                 11      8.2   2015-02-01 
A                9.8     6.5    2015-02-04
B                 10      8     2015-04-01
B                 9.9    9.5    2015-04-15
C                 8.7    2.3    2015-02-01

now I want query which show output

company name    open    close  difference  date
A                9.8     6.5     3.3       2015-02-04
B                 10      8       2        2015-04-01
c                 8.7     2.3    6.4       2015-02-01

Answer 1

A simple INNER JOIN of your original table to a subquery obtaining the maximum differences for each company is all you need.

SELECT c1.companyname, c1.open, c1.close, c2.maxTime AS difference, c1.date
FROM companies c1
INNER JOIN
(
    SELECT companyname, MAX(open - close) AS maxTime
    FROM companies
    GROUP BY companyname
) c2
ON c1.companyname = c2.companyname AND (c1.open - c1.close) = c2.maxTime

Follow the link below for a running demo (courtesy of @lc.):

SQLFiddle