Use of maxdate? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use of maxdate?

Author

Topic

Jamhin4
Starting Member

4 Posts

Posted - 2010-02-09 : 20:45:06

Below is my basic starting query and results. What I need to pull is the most current Facility Status, so all I want to pull in the end is the top row, shown in the results. I attempted to use
MAX(dbo.tblFacilityStatusHistory.LastUpdated) AS MAXLastUpdated
in the select statement and ended up with more rows than I wanted.

Query:
SELECT dbo.tblFacilityStatusHistory.FacilityNumber AS Fac#, dbo.refFacilityStatus.FacilityStatusDesc,
dbo.tblFacilityStatusHistory.LastUpdated
FROM dbo.tblFacilityStatusHistory INNER JOIN dbo.refFacilityStatus ON dbo.tblFacilityStatusHistory.FacilityStatusID = dbo.refFacilityStatus.FacilityStatusID
WHERE (dbo.tblFacilityStatusHistory.FacilityNumber = '30044')
ORDER BY dbo.tblFacilityStatusHistory.LastUpdated DESC

Results:
Fac# FacilityStatusDesc LastUpdated
30044 Regular 01/12/2010 13:58
30044 Closed 01/12/2010 13:58
30044 Probationary Provisional 01/11/2010 13:19
30044 Closed 01/11/2010 13:03
30044 New Provisional 01/11/2010 13:03
30044 Closed 01/11/2010 10:31
30044 New Provisional 01/04/2010 11:18
30044 Pending Application 11/18/2009 10:17

Can someone please show me how to write my query out so it only pulls that first row??? I've been battling with this thing for hours, trying to make the MAX thing work, but always end up with the max of 01/12, 01/11 and then it also includes 01/04 and 11/18 each time.

HUGE THANKS to anyone who can help me!!!
Jamie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-09 : 20:46:53

SELECT TOP 1 . . . . ?

KH
[spoiler]Time is always against us[/spoiler]