SQL help

Posted on 2011-05-03
Last Modified: 2013-11-05
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?
    LVL 42

    Accepted 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#


    Author Comment

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

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now