[Solved] Return highest MAX "date/time" only

[dewayne ashley]

Have a Table that stores "ID(Primary Key AUTO)" "Id #" "Date / Time" "Location".
When a Id # is moved to a new Location I do not want to update its Location, I want to insert a new record, thus you can see if needed, where it was then and where it is now. Simple

I am having lots of trouble designing a query that can return only 1 current Location per Id # by Max "Date/Time" because when function MAX the "Date / Time" column , I also have to Group the rest of the columns which in turn returns the other entries for the same Id # also
I only need the the most recent entry to be returned.


Any Ideas?
Thank you

[FJCC]

Try something like

Code: Select all

SELECT  "Id #", "Date / Time", "Location" 
FROM "MyTable" As "MainTable" INNER JOIN
(SELECT "Id #", MAX("Date / Time") As "MaxDate" FROM "MyTable"
    GROUP BY "Id #") AS "MaxQuery"
ON "MainTable"."Id #" = "MaxQuery"."Id #" AND "MainTable"."Date / Time" = "MaxQuery"."MaxDate" 

The sub query finds the max date for each id and the INNEr JOIN filters the main table to return just those combinations of ids and dates.

[dewayne ashley]

will try

[dewayne ashley]

Try something like

Code: Select all

SELECT  "Id #", "Date / Time", "Location" 
FROM "MyTable" As "MainTable" INNER JOIN
(SELECT "Id #", MAX("Date / Time") As "MaxDate" FROM "MyTable"
    GROUP BY "Id #") AS "MaxQuery"
ON "MainTable"."Id #" = "MaxQuery"."Id #" AND "MainTable"."Date / Time" = "MaxQuery"."MaxDate" 

The sub query finds the max date for each id and the INNEr JOIN filters the main table to return just those combinations of ids and dates.

Many Thanks, Exactly what I was hoping to find. Will mark as SOLVED.