view raw
Dm3k1 Dm3k1 - 6 months ago 33
SQL Question

Select Last Updated Row with condition

I'm working on building a workload tracking system, I have a table that currently has listed all the tasks to be completed (each with a unique ID), but also has all the updates with a datestamp so that I can track how long it took for the status to be updated.

My dilemma is that for a form I want to query only the latest update, currently the select query shows both the original task and the updated task separately.

In words, I guess what I need to do is to select only a task given that the ID is the last one with that same task number (which is different than the ID, there will be duplicates when it is updated)

So if I have:

ID Task Date
1 A 4/30/13
2 B 5/2/13
3 A 5/3/13

That the table only shows:

ID Task Date
3 A 5/3/13
2 B 5/2/13

How can I do this? I think I'm missing something simple...


There are multiple ways to approach this query, even in Access. Here is a way using in with a subquery:

select t.*
from t
where in (select MAX(id) as maxid
               from t
               group by task
order by task

The subquery finds the maximum ids for all the tasks. It then returns the rows from the original table that match those ids.