How to get max(colName) value where colName is of Type TimeStamp in mySQL

I am having following table structure

and having the following Data in this table

Now I want to query in such a way so that I can have values of all the columns + the value of max(currenttime) for a particular game_id.

i am running the following query SELECT _id, game_id, inning_id, scoretype, oponentonescore, oponenttwoscore, currenttime, MAX( cu开发者_StackOverflowrrenttime ) AS latest FROM game_scores WHERE game_id = '2268'

and as a result i am getting only one row as in the following result

But i want want all rows matching the criteria(i.e. game id 2268)

How to compose Query to achieve this?

Thanks in advance

---

Your MAX() is an aggregate type function and gets one value for multiple rows.

So remove it and use something like this with PHP...

$maxTime = 0;

foreach($rows as $row) {
   $maxTime = max($maxTime, strtotime($row['currenttime']));
}

...or run another query, though the above should be sufficient...

SELECT MAX(`currenttime`) AS `latest`
  FROM `game_scores`
 WHERE `game_id` = '2268'

Formatted in your convenience OMG Ponies style.

---

i'm sorry, i haven't actually tested this, but maybe something like this would do the trick:

SELECT
    _id,
    game_id,
    inning_id,
    scoretype,
    oponentonescore,
    oponenttwoscore,
    currenttime,
    latest
FROM
    game_scores
JOIN (
    SELECT
        MAX(currenttime) AS latest
    FROM
        game_scores
    WHERE
        game_id = '2268'
) AS latest_calculation
WHERE game_id = '2268';

although this solution is heavier on the database than it is heavy on the application to figure it out after the query is back from the database (like alex suggested).

---

Something on the lines of this query would do the required. NOT TESTED.

 SELECT _id, game_id, inning_id, scoretype, oponentonescore, oponenttwoscore,
    currenttime,maxtime from game_scores OUTER join (SELECT _id,MAX(currenttime)
    as maxtime from game_scores) as t on t._id = game_scores._id  where game_id = '2268';