User Tools

Site Tools


db:sql_get_max_group

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

db:sql_get_max_group [2013/03/16 17:40] (current)
Line 1: Line 1:
 +==== SQL Script - Getting MAX/MIN from a group of records ====
 +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:
 +
 +<code sql>
 +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;​
 +</​code>​
 +
 +We populate it with some records:
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +\\ 
 +\\ 
 +\\ 
 +
 +  * **QUERY: We want highest invoice date for every patient**
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +
 +  * **QUERY: We want highest invoice value for every patient**
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +
 +  * **QUERY: We want total invoices value for every patient (and a big total)**
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +
 +  * **QUERY: We want the invoice (full details) with the highest date for every patient**
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +
 +  * **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:
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +If we add to the conditions another one: if multiple results, then take the latest invoice (highest date), we need to modify as:
 +
 +<​code>​
 +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)
 +</​code>​
db/sql_get_max_group.txt ยท Last modified: 2013/03/16 17:40 (external edit)