Help Pulling Max Date

[gjmac2]

Sample of Table data:

Name of Table: LG_LAB_RESULTS

SAMPLEDATETIME CREATINE ID#

2011-10-02 06:43:00.000 2.5 191613
2011-10-02 16:27:00.000 2.6 191613
2011-10-01 04:15:00.000 1.6 191613
2011-09-30 03:20:00.000 1.8 191613
2011-10-01 19:44:00.000 1.8 191613
2011-10-02 02:41:00.000 2.3 191613
2011-10-02 11:51:00.000 2.7 191613
2011-10-02 19:34:00.000 2.5 191613
2011-09-28 21:45:00.000 0.9 191613
2011-09-29 00:24:00.000 0.9 191613

I need to pull the creatine value based on the max date, in this case it would be 2.5 based on the date of 2011-10-02 19:34:00.000. I cannot figure out the command to do this. Any help would be much appreciated.

 

[gmmastros]

try this:

Select	SAMPLEDATETIME, CREATINE, ID
From    (
        Select *, Row_Number() Over(Partition By ID Order By SAMPLEDATETIME Desc) As RowId
        From   [!]YourTableNameHere[/!]
        ) As A
Where	RowId = 1

If this works, and you would like me to explain it, just let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gjmac2]

Does not work. I just need to pull the value of the creatinine field based on the max sampledate. I don't need to include the id. Thank for your help.

 

[gmmastros]

How about this:

Select	Top 1 Creatine
From    [!]YourTableNameHere[\!]
Order By SAMPLEDATETIME DESC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Select    Top 1 Creatine
From    [!]YourTableNameHere[/!]
Order By SAMPLEDATETIME DESC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Mike Lewis]

I had a similar problem last week. I solved it like this:

SELECT creatine from  LG_LAB_RESULTS
  WHERE sampledatetime = 
    (SELECT max(sampledatetime) FROM  LG_LAB_RESULTS)

That worked for me, but I'm not sure if it's the best solution. In particular, it assumes that the maximum value is unique.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[gjmac2]

Thank you guys. I got it worked out. I appreciate the help.

 

[SQLBill]

Post your solution here. That way if someone else has a similar question, the will find your solution which might work for them also.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gjmac2]

Well, what I did was to run to seperate querys to pull the creatinine value for the maximum sampledatetime. Using the queires, I created a table to then use with another table.