CODEWITHSUNDEEP
mysql
user114671
user114671

Reputation: 532

MAX() not showing expected result in MySQL

Really stumped here, so any advice would be much appreciated please.

I in the clients table, I have the following records for the client with ID = 257:

SELECT ref, ID, client_name, data_1 FROM clients WHERE ID = 257

+------+-----+-------------+--------+
| ref  | ID  | client_name | data_1 |
+------+-----+-------------+--------+
| 1123 | 257 | Client257   | 0      |
| 1124 | 257 | Client257   | 163    |
+------+-----+-------------+--------+

Now, if I add a simple MAX() to the ref field, I get:

SELECT MAX(ref), ID, client_name, data_1 FROM clients WHERE ID = 257

+------+-----+-------------+--------+
| ref  | ID  | client_name | data_1 |
+------+-----+-------------+--------+
| 1124 | 257 | Client257   | 0      |
+------+-----+-------------+--------+

I should get:

+------+-----+-------------+--------+
| ref  | ID  | client_name | data_1 |
+------+-----+-------------+--------+
| 1124 | 257 | Client257   | 163    |
+------+-----+-------------+--------+

Can anyone explain this please? And what should I do to get the desired result?

EDIT:

Added the output of SHOW CREATE TABLE clients as requested by @Ilion:

CREATE TABLE `clients` (
 `ref` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `ID` int(11) unsigned NOT NULL,
 `client_name` varchar(75) DEFAULT NULL,
 `data_1` int(11) unsigned NOT NULL DEFAULT '0',
 `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY `ref` (`ref`)
) ENGINE=MyISAM AUTO_INCREMENT=1135 DEFAULT CHARSET=utf8

Upvotes: 0

Views: 165

Answers (3)

Ilion
Ilion

Reputation: 6882

Have you tried a having clause? This should work:

SELECT ref, ID, client_name, data_1 FROM clients WHERE ID = 257 having max(ref);

It's the best way to make a selection based on an aggregate result when you don't actually want grouping.

Upvotes: 0

neohope
neohope

Reputation: 1832

does this help?

SELECT ref, ID, client_name, data_1 FROM clients WHERE ID = 257 and ref in
    (select max(ref) from clients WHERE ID = 257)

Upvotes: 2

paulslater19
paulslater19

Reputation: 5917

SELECT ref, ID, client_name, data_1 FROM clients WHERE ID = 257 ORDER BY ref DESC LIMIT 1

MAX() will show the maximum value for that column. Mysql was then picking the rest of the data up from the first row.

Rather, this query will make sure you are getting the row's data with the highest ref

Upvotes: 3

Related Questions