Query for Max(Date) in Sql Server 2005

Posted on 2007-10-09
Last Modified: 2008-02-07

This should be simple. I have a table like this:

myTable (PkDate, myInt1, myInt2, myInt3)

PkDate: Primary key of type datetime
myInt[1-3]: Int types

Some sample entries to the table can be:
10/1/2007, 8, 12, 20
10/3/2007, 9, 24, 28
10/5/2007, 4, 8, 24

Now, I want the latest date for ANY number that was entered in either myInt1, myInt2 or myInt3.

For instance, if I wanted to find the last date of when 8 was entered, I can type this query:
SELECT Max(PkDate) FROM myTable WHERE '8' In (myInt1, myInt2, myInt3)

I want a query that gives all the numbers' last entry date, not just 8's. So the query should return something like:

4, 10/1/2007
8, 10/5/2007
9, 10/3/2007
12, 10/1/2007

If it helps any, here are the rules for the table:
Int3 > Int2 > Int1 (Int3 is greatest, Int2 is between Int3 and Int1, Int1 is the smallest)
MAX Int3 is 28 (Int3 cannot be higher than 28)

Question by:freezegravity

    Author Comment

    woops, there was an error in what the query should result in

    ... it should be

    4, 10/5/2007 (10/5/2007 is the most recent date that 4 was entered)
    LVL 42

    Accepted Solution

    Simple, it is:

    Select Int1, max(PKdate) from
    Select PKdate, Int1 from myTable
    Select PKdate, Int2 from myTable
    Select PKdate, Int3 from myTable
    ) tmp
    group by int1
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    more efficient, because avoiding the multiple table scans, would be this:

    select pkdate, case when myint1 > myint2 and myint1 > myint3 then myint1 when myint2 > myint1 and myint2 > myint3 then myint2 else myint3 end as MaxInt
    from yourtable

    Author Comment

    THANKS both!

    Each solution had its own value!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video discusses moving either the default database or any database to a new volume.

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now