SQL: MAX (date) with wrong results

Question

SQL: MAX (date) with wrong results

Navigation

#1 by (2 votes)

3

Good malt, I have a problem, when searching for the highest date grouping by id, I get 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 right dates 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 for the help.

mysql sql

asked by anonymous 07.12.2016 / 16:26

1 answer

How to send text formatted with TidMessage smtp and richedit Know which checkbox is clicked

score 2 · Accepted Answer

The correct would be you to do the validation with a NOT EXISTS bringing only the record with the largest 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 subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery returns any line, EXISTS will be TRUE, and NOT EXISTS will be FALSE