Cant get a grip of this query

[Magnus Andersson]

Hi,

I can't really get this query going. It's a "select max max value of each group" problem.

I have duplicate rows in my database and I want to select the latest by selecting the row with the largest Id.

SELECT
MAX(statusTable.Id) AS BiFiStatusId, squaresTable.Id AS BiFiSquareId, statusTable.Status AS BiFiStatus, squaresTable.LatNW AS LatNW , squaresTable.LngNW AS LngNW
FROM BiFiStatus AS statusTable
INNER JOIN BiFiSquares AS squaresTable ON (squaresTable.Id = statusTable.BiFiSquareId)
WHERE statusTable.TimeStamp = '2012-04-13 12:00' AND squaresTable.Sweden = 1 AND squaresTable.Type = 3 AND statusTable.Status IN ([1,2])
GROUP BY squaresTable.Id

But this only gives me the largest Id, the other values in the row doesn't come from the same row as Id.

My feeling is that I have to do something like this:

SELECT *
FROM `AtmosphericData` t1
LEFT OUTER JOIN AtmosphericData t2 ON ( t1.AreaId = t2.AreaId
AND t1.DateAndTime = t2.DateAndTime
AND t1.ForecastTime < t2.ForecastTime )
WHERE t2.DateAndTime IS NULL
AND t1.`AreaId` =31727
AND t1.`DateAndTime` >= '2013-02-06 06'
AND t1.`DateAndTime` <= '2013-02-07 00'

But I cant get it to work when I have to inner join another table.

Any suggestions?