• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

SQL help

Hi Experts,

I have 1 table, with historic data. The table contains development tasks. Each task has a unique id (A number).
This means that this table has a record for each change to a task and a time stamp. What i need is to create some statistic numbers.

What i do is i that i finds some criteria like "WHERE Status<7 and Start_Date is null and registration_date<#02/28/2011#"

But these criteria should only be used on the latest record for each development task(the unique number) and within the criteria above.

Can i get some help with creating a  query that can do this?
1 Solution
create a stored query like this:

Select t1.* from yourtable as t1 where
   t1.yourtimestamp =
        (select max(t2.yourtimestamp) from yourtable as t2 where t2.uniqueId = t1.uniqueID)

That query produces a resultset of only the latest rows.  Then run your other SQL against the stored query instead of the complete table.  i.e.

Select * from yourquery
   WHERE Status<7 and Start_Date is null and registration_date<#02/28/2011#

DCRAPACCESSAuthor Commented:
Yes of course, how cloud i forget that. Thanks for your time

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now