01.01.2011

Let's assume that we have a table with invoices, and we want to get the invoice with highest date for every customer.

Our table is:

CREATE TABLE IF NOT EXISTS `invoices` (
  `customer_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `invoice_value` float NOT NULL,
  `invoice_date` date NOT NULL,
  KEY (`customer_id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We populate it with some records:

mysql> SELECT * FROM invoices;
+-------------+------------+---------------+--------------+
| customer_id | invoice_id | invoice_value | invoice_date |
+-------------+------------+---------------+--------------+
|           1 |       1000 |         12.21 | 2000-01-04   |
|           1 |       1002 |            22 | 2000-04-01   |
|           1 |       1005 |            50 | 2001-05-24   |
|           2 |       1008 |          24.1 | 2000-05-05   |
|           2 |       1009 |            45 | 2001-04-07   |
+-------------+------------+---------------+--------------+
5 rows in set (0.00 sec)

• QUERY: We want highest invoice date for every patient

mysql> SELECT customer_id, MAX(invoice_date) FROM invoices GROUP BY customer_id;
+-------------+-------------------+
| customer_id | MAX(invoice_date) |
+-------------+-------------------+
|           1 | 2001-05-24        |
|           2 | 2001-04-07        |
+-------------+-------------------+
2 rows in set (0.02 sec)

• QUERY: We want highest invoice value for every patient

mysql> SELECT customer_id, MAX(invoice_value) FROM invoices GROUP BY customer_id;
+-------------+--------------------+
| customer_id | MAX(invoice_value) |
+-------------+--------------------+
|           1 |                 50 |
|           2 |                 45 |
+-------------+--------------------+
2 rows in set (0.00 sec)

• QUERY: We want total invoices value for every patient (and a big total)

mysql> SELECT customer_id, SUM(invoice_value) FROM invoices GROUP BY customer_id WITH ROLLUP;
+-------------+--------------------+
| customer_id | SUM(invoice_value) |
+-------------+--------------------+
|           1 |    84.210000038147 |
|           2 |   69.1000003814697 |
|        NULL |   153.310000419617 |
+-------------+--------------------+
3 rows in set (0.00 sec)

• QUERY: We want the invoice (full details) with the highest date for every patient

mysql> SELECT a.customer_id, a.invoice_id, a.invoice_value, a.invoice_date FROM invoices a
    -> INNER JOIN (
    ->     SELECT customer_id, MAX(invoice_date) AS invdate  FROM invoices GROUP BY customer_id 
    -> ) AS x ON x.customer_id = a.customer_id  AND x.invdate = a.invoice_date;
+-------------+------------+---------------+--------------+
| customer_id | invoice_id | invoice_value | invoice_date |
+-------------+------------+---------------+--------------+
|           1 |       1005 |            50 | 2001-05-24   |
|           2 |       1009 |            45 | 2001-04-07   |
+-------------+------------+---------------+--------------+
2 rows in set (0.00 sec)

• QUERY: We want the invoice (full details) with the highest value for every patient

If we introduce another record, with invoice_id:1010 for customer_id:1 with the invoice_value:50 (the same as 1005), we'll have both invoices in the results:

mysql> SELECT a.customer_id, a.invoice_id, a.invoice_value, a.invoice_date FROM invoices a INNER JOIN (
    ->        SELECT customer_id, MAX(invoice_value) AS maxval, invoice_date FROM invoices GROUP BY customer_id
    -> ) AS x ON x.customer_id = a.customer_id AND x.maxval = a.invoice_value;
+-------------+------------+---------------+--------------+
| customer_id | invoice_id | invoice_value | invoice_date |
+-------------+------------+---------------+--------------+
|           1 |       1005 |            50 | 2001-05-24   |
|           1 |       1010 |            50 | 2010-09-22   |
|           2 |       1009 |            45 | 2001-04-07   |
+-------------+------------+---------------+--------------+
3 rows in set (0.00 sec)

If we add to the conditions another one: if multiple results, then take the latest invoice (highest date), we need to modify as:

mysql> SELECT a.customer_id, a.invoice_id, a.invoice_value, a.invoice_date FROM invoices a 
    -> INNER JOIN (
    ->          SELECT customer_id, MAX(invoice_value) AS maxval, MAX(invoice_date) AS invdate FROM invoices GROUP BY customer_id
    -> ) AS x ON x.customer_id = a.customer_id AND x.maxval = a.invoice_value AND x.invdate = a.invoice_date;
+-------------+------------+---------------+--------------+
| customer_id | invoice_id | invoice_value | invoice_date |
+-------------+------------+---------------+--------------+
|           1 |       1010 |            50 | 2010-09-22   |
|           2 |       1009 |            45 | 2001-04-07   |
+-------------+------------+---------------+--------------+
2 rows in set (0.00 sec)