Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-20 : 09:14:31
|
| Ryan Flatters writes "Windows 2000 Advanced ServerSQL Server 2000I am having trouble working out a way to use the MAX function to retrieve values from an OPENQUERY statement. I want to search values returned from an OPENQUERY statement for the Maximum date and within that the Maximum time. In English...I want to know the last known location for a patient chart in a chart tracking application at a healthcare institution. In order to find the last known location I thought I could search for the Maximum date and within that Maximum date find the record that has the Maximum time.Most of my attempts yeild the maximum time for the entire table rather than the maximum time for the specific chart number.Am I going down a garden path with the MAX statement? Should I be looking at another solution?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-20 : 09:57:32
|
| Are date and time stored separately (different columns) or as a single datetime value? If it's a single column, then using Max() will give you both.Can you post the SQL OPENQUERY statement you're using now, and what type of data source you're linking to (SQL Server, Oracle, Access, etc.) The following should work if the data source supports subqueries:SELECT C.ChartNum, C.Location, C.LogDateFROM ChartInfo C INNER JOIN(SELECT ChartNum, Max(LogDate) MaxDate FROM ChartInfo GROUP BY ChartNum) AS MON (C.ChartNum=M.ChartNum AND C.LogDate=M.MaxDate)The inner query finds each chart and it's maximum date, then joins these results back to the chart table to find the location for that chart at that time. |
 |
|
|
|
|
|
Finding Nested Maximum Values With OpenQuery