MySQL query returning 0 when the real value is not 0

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 May 27 '17 at 00:16 — Juan Carlos Oropeza, May 27 '17 at 00:16
How can I get the value with the highest id? – sleepless_in_seattle May 27 '17 at 00:16 — sleepless_in_seattle, May 27 '17 at 00:16

score 1 · Accepted Answer · answered May 27 '17 at 00:26

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 '17 at 00:26

z m

1,493
2
19
21

MySQL query returning 0 when the real value is not 0

1 Answers1