Dm3k1 Dm3k1 - 1 year ago 71
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...

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download