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

Question

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.

"My requirement is to get the row of the latest barcode_no." -- You cannot get rows from the table using GROUP BY. GROUP BY computes new rows using the data from the table. Take a look at this answer on a similar question. — axiac
– axiac, Commented Aug 16, 2017 at 7:54
Thank you both axiac and Sagar Gangwal. Seems to be similar axiac. To bad it did not show when searched. — Roggie
– Roggie, Commented Aug 17, 2017 at 8:38

halfer · Accepted Answer · 2017-08-22 14:53:53Z

3

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

edited Aug 22, 2017 at 14:53

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Aug 16, 2017 at 7:44

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related