Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    9

    Unanswered: How to find latest status records?

    I have a status table that holds multiple status entries for an entity, like...

    Code:
        OwningEntity    Status ID     StatusUpdated
         -------------------------------------------
               1                   1             23/01/10
               1                   2             23/02/10
               1                   4             23/04/10
               2                   4             19/02/10
               3                   1             21/02/10
               3                   1             21/03/10
               4                   3             23/01/10
               4                   4             11/04/10
    I'm struggling to define a valid query that returns just the latest status entries for each owning entity based around the most recent 'StatusUpdated'. This should produce the result set...
    Code:
        OwningEntity    Status ID     StatusUpdated
         -------------------------------------------
               1                   4             23/04/10
               2                   4             19/02/10
               3                   1             21/03/10
               4                   4             11/04/10
    Any suggestions?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Something like:
    Code:
    SELECT st.owningentity,
           st.status_id,
           st.statusupdated
    FROM status_entries st
    WHERE statusupdated = (SELECT max(st2.statusupdated)
                           FROM status_entries st2
                           WHERE st2.owningentity = st.owningentity);

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think a join will tend to be more efficient:
    Code:
    SELECT st.OwningEntity
         , st.StatusID
         , st.StatusUpdated
    FROM   status_table As st
     INNER
      JOIN (
            SELECT OwningEntity
                 , Max(StatusUpdated) As LatestStatusUpdated
            FROM   status_table
            GROUP
                BY OwningEntity
           ) As latest
        ON latest.OwningEntity = st.OwningEntity
       AND latest.LatestStatusUpdated = st.StatusUpdated
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gvee View Post
    I think a join will tend to be more efficient:
    Why's that G?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    After testing, I retract my sentiments and replace them with "I find JOINs easier to write".

    I'm clearly out of practice
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    joining to a derived table is generally more efficient than a correlated subquery (and never worse)

    i've seen this many times in database forum discussions, but i haven't bookmarked any of them

    so i just now went off to google to see if i could find any

    Speed Tips For SQL Server SELECT Statements
    MySQL vs. MSSQL – Derived Table Performance John Stanfield dot com
    http://joinfu.com/presentations/inde...timization.pdf (PDF, see p.33)

    and this last one which contains a great quote...
    The Baker's Dozen: 13 Productivity Tips for Database Development Using Transact-SQL
    Everyone knows the old joke about getting ten economists in a room and getting ten different views of economics. Although that's somewhat of an exaggeration, you could ask ten different developers which approach is preferable, and you'd likely see some votes for the correlated subquery approach because of readability, some votes for the derived table approach on the basis of better potential performance, and no doubt a few votes for still another alternative.

    Developers of similar strength and experience can and will disagree on the emphasis of performance (or the promise of performance) versus maintenance and readability. Sometimes there is no one single correct answer. The key is to apply consistency in approaching these types of situations.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •