SQL: MAX(data) with wrong results

Asked

Viewed 98 times

3

Good guys, have a problem, when searching for the highest date by 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 |            |
+------------------+----------+--------+------------+------------+

Thanks in advance for your help.

mysql sql

  • Can put the script for creating and inserting tables?

  • I didn’t create the table, but I think it looks like this:create table chklist ( id_chklist int(11), id_sk int(11) , n_func varchar(100), data date, Obs varchar(100), PRIMARY KEY (id_chklist ), FOREIGN KEY (id_sk) REFERENCES Persons(id_sk) )

  • In which FOREIGN KEY can be multi

  • I added an answer, see if it fits you

1 answer

2


The correct thing would be for you to do the validation with a NOT EXISTS bringing only with 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

Or in free translation:

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

  • you Rock! just don’t understand.. b. date > a.date is what defines the longest date?

  • 1

    Yeah. I’m actually checking to see if there’s a record with the same keys but it has a longer date. Leaving only the record with longer date

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.