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

Query for Max(Date) in Sql Server 2005

Hello:

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)

THANKS!
0
freezegravity
Asked:
freezegravity
  • 2
2 Solutions
 
freezegravityAuthor Commented:
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)
0
 
dqmqCommented:
Simple, it is:


Select Int1, max(PKdate) from
(
Select PKdate, Int1 from myTable
union
Select PKdate, Int2 from myTable
union
Select PKdate, Int3 from myTable
) tmp
group by int1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
freezegravityAuthor Commented:
THANKS both!

Each solution had its own value!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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