Problems with the following MySQL query

Navigation
3

I have a table in MySql with the following data, where id_pers is a foreign key to another table people. 

--------------------------------------------
  id   |  id_pers  |    created_at         |
--------------------------------------------
   1   |      1    | 2016-09-12 16:59:11   |
--------------------------------------------
   2   |      1    | 2016-09-12 17:03:45   |
--------------------------------------------
  3    |      1    | 2016-09-12 17:03:30   |
--------------------------------------------
  4    |      2    | 2016-09-12 18:05:29   |
--------------------------------------------
  5    |      2    | 2016-09-12 18:03:00   |
--------------------------------------------

How can I get the last inserted record grouped by id_pers . The selection that I am making is: 

select id, id_pers, max(created_at) as fecha_creacion
from observaciones group by id_pers

What it gives me back: 

--------------------------------------------
  id   |  id_pers  |    created_at         |
--------------------------------------------
  1    |      1    | 2016-09-12 17:03:45   |
--------------------------------------------
  4    |      2    | 2016-09-12 18:05:29   |
--------------------------------------------

It returns a id=1 in the first record when it should be id=2 . However the id=4 gives it back to me well.

Columns are of type id => INT , id_pers => INT and created_at => TIMESTAMP .

Thanks in advance.

    
asked by Developer 12.09.2016 в 18:59
source

2 answers

4

What you need is to get the maximum date by id_pers and then you can make a JOIN with the original table: 

SELECT om.*
FROM observaciones o
INNER JOIN (SELECT id_pers, MAX(created_at) max_created
            FROM observaciones
            GROUP BY id_pers) om
    ON o.id_pers = om.id_pers
    AND o.created_at = om.max_created;
    
answered by 12.09.2016 / 19:03
source
3

Why the question code does not work:

  • Detection of Functional Dependence ►

I know I know little about MySQL , but I would like to collaborate with another alternative, using subqueries without JOIN : 

SELECT *
FROM observaciones t
WHERE t.id =
(
    SELECT id
    FROM observaciones t1
    WHERE t1.id_pers = t.id_pers
    ORDER BY created_at DESC
    LIMIT 1
);

Code in use: ►

That code I could do thanks to the following documentation:

I did not understand why MySQL allows to include in the SELECT fields that are not in GROUP BY , but the doubts were cleared when reading:

  • GROUP BY Modifiers ►
  • MySQL Handling of GROUP BY ►
answered by 13.09.2016 в 09:04
Problem with struct in C ++ Order of the elements in Bootstrap 4