MySQL query returning 0 when the real value is not 0 [duplicate]

Question

Table:

CREATE TABLE `test_table` (
  `id` int(10) UNSIGNED NOT NULL,
  `field` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `value` text COLLATE utf8_swedish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

INSERT INTO `test_table` (`id`, `field`, `value`) VALUES
(1, 'first_name', '0'),
(2, 'last_name', '0'),
(3, 'first_name', '1'),
(4, 'last_name', '1');

Query:

SELECT MAX(`id`), `field`, `value` FROM test_table GROUP BY `field`

To my understand this should return the value of each fields with the higher id.

But it's returning:

MAX(`id`)    field          value
3            first_name     0
4            last_name      0

It's returning the correct id, the correct fields but the wrong values. What's going on here and how can I fix it to return rows 3 and 4?

Your understanding is wrong. Will return a random value.

Juan Carlos Oropeza
– Juan Carlos Oropeza

2017-05-27 00:16:10 +00:00
Commented May 27, 2017 at 0:16 — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 27, 2017 at 0:16
How can I get the value with the highest id?

sleepless_in_seattle
– sleepless_in_seattle

2017-05-27 00:16:39 +00:00
Commented May 27, 2017 at 0:16 — sleepless_in_seattle
– sleepless_in_seattle, Commented May 27, 2017 at 0:16

z m · Accepted Answer · 2017-05-27 00:26:46Z

1

SELECT t.`id`, t.`field`, t.`value` 
FROM test_table t
JOIN (SELECT MAX(`id`) AS `id`, `field` 
      FROM test_table 
      GROUP BY `field`) t1 ON t1.`id` = t.`id`

answered May 27, 2017 at 0:26

z m

1,5132 gold badges21 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

MySQL query returning 0 when the real value is not 0 [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related