CODEWITHSUNDEEP
mysqlsql
Acubi
Acubi

Reputation: 2783

Error on getting corresponding dateTime of max value

Table 'test'

+------+--------------------+
|value |    dateTime        |
+------+--------------------+
|19    |2011-12-22 11:09:42 |
+------+--------------------+
|16    |2011-12-22 05:09:00 |
+------+--------------------+
|2     |2011-12-22 07:09:42 |
+------+--------------------+
|30    |2011-12-22 10:09:15 |
+------+--------------------+
|45    |2011-12-22 03:09:42 |
+------+--------------------+

I would like to get the Max value and its corresponding dateTime. In this case, the final result should be: max value:45 and dateTime:2011-12-22 03:09:42. I used 

SELECT MAX( `value` ) , `dateTime`
FROM `test`

After running the above sql statement, the max 'value' is correct (i.e. 45), but the 'dateTime 'is not correct (i.e.2011-12-22 11:09:19)

Is there anyone can help me? Many thanks!

Upvotes: 1

Views: 271

Answers (3)

Umbriël
Umbriël

Reputation: 1

There is a bug in MySQL in version 5.5. Try upgrading MySQL. Your code is not the problem.

http://bugs.mysql.com/bug.php?id=54784

Upvotes: 0

juergen d
juergen d

Reputation: 204784

try this:

SELECT TOP 1 `value`, `dateTime`
FROM `test`
ORDER BY `value` DESC

Upvotes: 3

Michał Powaga
Michał Powaga

Reputation: 23183

-- general
select *
from test
where value= (select max(value) from test)

or

--tsql
select top 1 *
from test
order by value desc

or

--mysql
select *
from test
order by value desc
limit 1;

Upvotes: 2

Related Questions