Roggie Roggie - 2 years ago 65
MySQL Question

using MAX() and GROUP BY is not returning correct result

Here is my DB.

id - date_time - barcode_no - result
1 - '2017-08-01 01:00:00' - 'A' - '1'
2 - '2017-08-01 02:00:00' - 'A' - '2'
31 - '2017-08-01 03:00:00' - 'A' - '3'
4 - '2017-08-01 04:00:00' - 'B' - '4'
5 - '2017-08-01 05:00:00' - 'B' - '5'
61 - '2017-08-01 06:00:00' - 'B' - '6'
7 - '2017-08-01 07:00:00' - 'C' - '7'
8 - '2017-08-01 08:00:00' - 'C' - '8'
9 - '2017-08-01 09:00:00' - 'C' - '9'
100- '2017-08-01 10:00:00' - 'C' - '10'


I have this query on mySQL

SELECT barcode_no,MAX(date_time),result
FROM test_data_main
GROUP BY barcode_no


but it does not give me the correct result. I also used the any_value(result) but I get the same wrong result.

Here is the link for my sample DB and code on SQL Fiddle.
SAMPLE CODE

My requirement is to get the row of the latest barcode_no.

id - date_time - barcode_no - result
31 - '2017-08-01 03:00:00' - 'A' - '3'
61 - '2017-08-01 06:00:00' - 'B' - '6'
100- '2017-08-01 10:00:00' - 'C' - '10'


but my wrong result was

id - date_time - barcode_no - result
31 - '2017-08-01 03:00:00' - 'A' - '1'
61 - '2017-08-01 06:00:00' - 'B' - '4'
100- '2017-08-01 10:00:00' - 'C' - '7'


I am not sure what is wrong with my query. Please advise.

Thanks in advance.

Answer Source
SELECT test_data_main.* FROM test_data_main
INNER JOIN
(
  SELECT barcode_no,MAX(date_time) date_time
  FROM test_data_main 
  GROUP BY barcode_no
)Z ON Z.barcode_no=test_data_main.barcode_no AND Z.date_time=test_data_main.date_time

You can try above code.

You can check here.Fiddle Demo

Hope this will help you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download