0

I got a query where I´m supposed to show the latest record of my users, I can filter by user or not. Here is an example of the data

id      fecha               no_emp
1326    2016-06-20 16:31:48 69976
1327    2016-06-20 16:31:38 69976
1328    2016-06-20 16:31:48 69976
1329    2016-06-21 17:22:37 69976
1330    2016-06-21 17:22:37 69976
1331    2016-06-27 13:06:22 69976

I´m supposed to get id 1331, and date 2016-06-27 13:06:22 But keep getting id 1326 and date 2016-06-20 16:31:48 if my query is:

SELECT id, fecha, no_emp
FROM of_direcciones
WHERE no_emp = '69976'
GROUP BY no_emp
order by ID DESC;

and I get id 13226 and date 2016-06-27 13:06:22 if my query is:

SELECT id, MAX(fecha), no_emp
FROM of_direcciones
WHERE no_emp= '69976'
GROUP BY no_emp;

What´s wrong in my query? Thanks in advance

linker85
  • 1,601
  • 5
  • 26
  • 44
  • 1
    If you want the 'latest' record based on the date, then you should order by the date, not the id, you can use `limit 1` to return just the one record. Also, the returned id is arbitrary because you're not grouping by all non-aggregate fields from your select list. MySQL allows this, other databases would throw an error. – Hart CO Jun 27 '16 at 18:55

2 Answers2

4

What is wrong with your query is that you are aggregating by no_emp and then choosing fields such as id which are not in the group by clause. MySQL returns values from indeterminate rows in this case. And, the indeterminate row might change, with each invocation of the query.

Here is a better way to get the latest record for a particular user:

select d.*
from of_direcciones d
where d.no_emp = '69976'
order by d.fecha desc
limit 1;

This can even take advantage of an index on of_direcciones(no_emp, fecha) for best performance.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You should obtain the max(fecha) and the filter by this value and no_emp

 SELECT id,  fecha, no_emp
 FROM of_direcciones
 where  fecha =  (select max(fecha,) from of_direcciones  
                  WHERE no_emp= '69976')
 AND  no_emp= '69976';
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107