MySQL groupwise MAX () returns unexpected results

Question

TABLE: LOAN

Loan_no Amount SSS_no Loan_date 7 700.00 0104849222 2010-01-03 8 200.00 0104849222 2010-02-28 9 300.00 0119611199 2010-11-18 10 150.00 3317131410 2012-11-28 11 600.00 0104849222 2011-01-03 14 175.00 3317131410 2012-12-05 15 260.00 3317131410 2013-02-08 16 230.00 0104849222 2013-03-06 17 265.00 0119611199 2011-04-30 18 455.00 3317131410 2013-03-10 

---

DESIRED RESULTS:

I would like to get the last access to the loan by each person (indicated by their SSS number). The results should be as follows:

 Loan_no Amount SSS_no Loan_date 16 230.00 0104849222 2013-03-06 17 265.00 0119611199 2011-04-30 18 455.00 3317131410 2013-03-10 

---

QUERY # 1 USED:

SELECT * FROM loan GROUP BY SSS_no ORDER BY Loan_date DESC

MYSQL RESULT

 Loan_no Amount SSS_no Loan_date 10 150.00 3317131410 2012-11-28 9 300.00 0119611199 2010-11-18 7 700.00 0104849222 2010-01-03 

---

QUERY # 2 USED:

SELECT Loan_no, Amount, SSS_no, max(Loan_date) FROM loan GROUP BY SSS_no

MYSQL RESULT

 Loan_no Amount SSS_no Loan_date 7 700.00 0104849222 2013-03-06 9 300.00 0119611199 2011-04-30 10 150.00 3317131410 2013-03-10 

Can someone help me with my problem? Thanks.

Answer 1

Try this instead:

 SELECT l1.* FROM loan AS l1 INNER JOIN ( SELECT SSS_no, MAX(Loan_date) LatestDate FROM loan GROUP BY SSS_no ) AS l2 ON l1.SSS_no = l2.SSS_no AND l1.loan_date = l2.LatestDate; 

SQL Fiddle Demo

This will give you:

 | LOAN_NO | AMOUNT | SSS_NO | LOAN_DATE | ---------------------------------------------- | 16 | 230 | 104849222 | 2013-03-06 | | 17 | 265 | 119611199 | 2011-04-30 | | 18 | 455 | 3317131410 | 2013-03-10 | 

Answer 2

The MySQL link offers several ways to solve this problem. The simplest is the subquery:

 SELECT * FROM loan l1 WHERE loan_date=(SELECT MAX(l2.loan_date) FROM loan l2 WHERE l1.sss_no = l2.sss_no); 

Given that this type of subquery has potentially poor performance , they also suggest using JOIN (essentially the answer of Mahmoud Gamal):

 SELECT l1.loan_no, l1.amount, l1.sss_no, l1.loan_date FROM loan l1 JOIN ( SELECT loan_no, MAX(loan_date) AS loan_date FROM loan GROUP BY sss_no) AS l2 ON l1.loan_date = l2.loan_date AND l1.sss_no = l2.sss_no; 

The third option:

 SELECT l1.loan_no, l1.amount, l1.sss_no, l1.loan_date FROM loan l1 LEFT JOIN loan l2 ON l1.sss_no = l2.sss_no AND l1.loan_date < l2.loan_date WHERE l2.sss_no IS NULL; 

LEFT JOIN works on the basis that when l1.loan_date has the maximum value, l2.loan_date appears, so the values ​​of the line l2 will be NULL.

All of them should have the same output, but probably differ in performance.

Answer 3

The reason you get unexpected results is because you use GROUP BY only one column in the SELECT list, and you don't use any aggregate functions in all columns.

MySQL uses an extension for the GROUP BY function, which can cause unexpected results if you are not GROUP BY or aggregate all the items in a SELECT list. (see MySQL Extensions for GROUP BY )

In MySQL Docs:

MySQL expands the use of GROUP BY so that the selection list can refer to non-aggregated columns not named in the GROUP BY clause .... You can use this function to improve performance by avoiding unnecessary sorting and grouping of columns. However, this is useful primarily when all the values ​​in each non-aggregated column not named in GROUP BY are the same for each group. The server can select any value from each group, therefore, if they do not match, the selected values ​​are undefined. Moreover, the selection of values ​​from each group cannot depend on the addition of an ORDER BY clause. The result set is sorted after the values ​​have been selected, and ORDER BY does not affect the values ​​that the server selects.

The only way to return the correct result is to correctly change your query to aggregate and GROUP BY .

So you can use something similar to this:

 select l1.loan_no, l1.amount, l1.SSS_no, l1.loan_date from loan l1 inner join ( select SSS_no, max(loan_date) Loan_date from loan group by SSS_no ) l2 on l1.SSS_no = l2.SSS_no and l1.loan_date = l2.loan_date 

See SQL Fiddle with Demo

This implements a subquery to get max(loan_date) for each SSS_no . This subquery is then joined to your table in both SSS_no and max loan_date , which ensures that you get the correct result for each SSS_no .