query help (MAX)

[ludmann]

Can someone please help me with this query.

zoneID, flyingObjectID and DateAndTime make up a composite key for my table FlyingObjects

I need to get the rows: for each zone all the flying objects associated with it (once), where the DateAndTime is MAX(DateAndTime)

so far I got:

SELECT zoneID, flyingObjectID, MAX(DateAndTime)
FROM FlyingObjects
GROUP BY zoneID, flyingObjectID
ORDER BY zoneID MAX(DateAndTime) desc

This is fine and it works well. However the information I need to select is not just zoneID, flyingObjectID and MAX(DateAndTime)
but also all other columns in the rows
like positionX, positionY, positionZ

I have been trying to use IN and EXISTS and use
the above SQL as a subquery, but it won't work.

Any help appreticated

Marika

 

[Golom]

You could do something like

SELECT zoneID, FlyingObjectID, DateAndTime, posX, posY, PosZ, etc. 

FROM FlyingObjects F 

Where F.DateAndTime = 
      (Select MAX(DateAndTime) From FlyingObjects M
       Where M.ZoneID = F.ZoneID AND
             M.FlyingObjectID = F.FlyingObjectID)

ORDER BY zoneID, DateAndTime DESC

 

[PHV]

Another way:
SELECT A.*
FROM FlyingObjects A INNER JOIN (
SELECT zoneID,flyingObjectID,MAX(DateAndTime) LastDate
FROM FlyingObjects GROUP BY zoneID,flyingObjectID
) B ON A.zoneID=B.zoneID AND A.flyingObjectID=B.flyingObjectID AND A.DateAndTime=B.LastDate
ORDER BY A.zoneID,A.DateAndTime DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ludmann]

Thank you, both