Help on difficult SQL statement needed

Posted on 2007-10-02
Last Modified: 2010-03-19
Hi experts,

I have the following table structure:
- Status_ID, bigint, PK
- Status_Name, varchar(50)
- Status_Date, DateTime
- Status_Is_Valid, bit

And here is the trick: only the LATEST status may be valid (Status_Is_Valid = true). All other status have to be invalid (Status_Is_Valid = false). In other word: on every INSERT and UPDATE I have to make sure that Status_Is_Valid is fixed for all records.

A few days ago I already got excellent help by Dennis Miller (dgmg). Thanks for that again, Dennis!
He suggested me to use the following statement to make sure that only one of my status entries is valid.

Update YourTable
   set status_is_valid =  
          case when YourTable.ID = m.ID then 1 else 0 end
   (Select max(ID) from YourTable T
        where T.status_date =
               (select max(status_date from YourTable)) m  

This seemed to work great in the beginning, but now I found out that this is still not enough: SQL server gets confused if there are two or more status with the same MAX(status_date) - and unfortunately this can happen. What I need here is a second criteria that takes over in case there is more than one MAX(status_date) status, let's say Status_ID. So, in other words: if there is more than one status having MAX(status_date) then set the one with MAX(status_id) to valid. Can this be achieved in some way by modifying the aboves sql statement?

Hope anybody of you can help me out of this again. Thanks so much!
Question by:softwarea
    LVL 18

    Accepted Solution

    Try This:

    update  YourTable
    set     Status_Is_Valid = case
        when (yourtable.status_id in (
            Select  max(T.Status_ID) as ID
            from    YourTable T
            where   T.status_date =
            (   select max(status_date)
                from   YourTable
                where  Status_Name = T.Status_Name)
            group  by Status_name
            )) then 1
        else 0

    Hope this helps ...

    Author Comment

    I do not know what to say: My first test was PERFECT! I hope this will hold on the next hours. But so far it is definetely an improve. And I had no idea on how to formulate this update statement. Will have a deeper look inside soon ;-)
    Thanks so much!!!
    LVL 18

    Expert Comment

    Glad I could be of any help and thanks for the grade !

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    733 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

    23 Experts available now in Live!

    Get 1:1 Help Now