Question:
Good guys, I have a problem, when looking for the highest date grouping by id, I get the wrong value in the obs field.
Query in use:
SELECT a.n_func,a.id_sk, a.data, a.obs, b.maxdata, b.obs
FROM chklist a
INNER JOIN (
SELECT n_func, id_sk, MAX(data) maxdata, obs
FROM chklist
GROUP BY id_sk, n_func
) b ON a.id_sk = b.id_sk AND a.n_func = b.n_func
where
a.n_func=123
group by a.id_sk, a.n_func
I get the dates right but the obs field should appear empty.
+--------+----------+------------+------------+------------+------------+
| n_func | id_sk | data | obs | maxdata | obs |
+--------+----------+------------+------------+------------+------------+
| 532619 | 11 | 2016-03-21 | | 2016-09-08 | |
| 532619 | 13 | 2012-10-08 | notfunc | 2016-11-29 | notfunc |
| 532619 | 152 | 2013-05-29 | | 2016-09-01 | |
| 532619 | 200 | 2015-06-09 | | 2016-06-13 | |
+--------+----------+------------+------------+------------+------------+
original table:
+------------------+----------+--------+------------+------------+
| id_chklist | id_sk | n_func | data | obs |
+------------------+----------+--------+------------+------------+
| 10607 | 13 | 532619 | 2012-10-08 | notfunc |
| 922723 | 13 | 532619 | 2013-05-29 | |
| 922724 | 152 | 532619 | 2013-05-29 | |
| 922875 | 152 | 532619 | 2013-07-16 | |
| 924753 | 152 | 532619 | 2014-01-17 | |
| 926760 | 152 | 532619 | 2014-08-21 | |
| 928527 | 152 | 532619 | 2015-02-23 | |
| 930059 | 200 | 532619 | 2015-06-09 | |
| 930635 | 152 | 532619 | 2015-08-28 | |
| 932124 | 200 | 532619 | 2015-12-15 | |
| 936359 | 13 | 532619 | 2013-11-29 | |
| 932955 | 152 | 532619 | 2016-03-01 | |
| 933179 | 11 | 532619 | 2016-03-21 | |
| 934392 | 200 | 532619 | 2016-06-13 | |
| 935026 | 152 | 532619 | 2016-09-01 | |
| 935197 | 11 | 532619 | 2016-09-08 | |
| 936372 | 13 | 532619 | 2014-05-29 | |
| 936382 | 13 | 532619 | 2014-11-29 | |
| 936503 | 13 | 532619 | 2016-05-29 | |
| 936504 | 13 | 532619 | 2016-11-29 | |
+------------------+----------+--------+------------+------------+
Thank you in advance for your help.
Answer:
The correct thing would be for you to validate with a NOT EXISTS bringing only the record with the highest date as follows:
SELECT a.n_func,
a.id_sk,
a.data,
a.obs
FROM chklist a
WHERE NOT EXISTS(SELECT b.n_func,
b.id_sk,
b.data,
b.obs
FROM chklist b
WHERE b.id_sk = a.id_sk
AND b.n_func = a.n_func
AND b.data > a.data)
AND a.n_func=123;
Subqueries with EXISTS or NOT EXISTS
If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE
Ou em tradução livre:
Se a subquery retornar qualquer linha, EXISTS será VERDADEIRO, e NOT EXISTS será FALSO