Query Latest Record? - 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

Query Latest Record?

Author

Topic

mikezdman
Starting Member

4 Posts

Posted - 2009-07-31 : 15:32:58

I have a view set up that includes a test year field. I am trying to set up the query to only return the records associeted with the newest test year. Here is the code I am using:

SELECT sdeuser.WATER_HYDRANT.SHAPE, sdeuser.WATER_HYDRANT.OBJECTID, sdeuser.WATER_HYDRANT.TAG, sdeuser.WATER_HYDRANT.SIZE_,
sdeuser.WATER_HYDRANT.MANUFACTURER, sdeuser.WATER_HYDRANT.CONNECTION_, sdeuser.WATER_HYDRANT.GND_ELEV,
sdeuser.WATER_HYDRANT.ELEVATION, sdeuser.WATER_HYDRANT.OWNER, sdeuser.WATER_HYDRANT.YEAR_INSTALLED,
sdeuser.WATER_HYDRANT.COMMENTS, sdeuser.WATER_HYDRANT.H20_NET, RMS.Test_Year, RMS.Years_Elasped, RMS.gpm_20psi, RMS.static,
RMS.residual, RMS.total_flow, RMS.press_drop, RMS.test_method, RMS.pitot_hydrant1, RMS.pitot_pitot1, RMS.pitot_opening1, RMS.pitot_discharge1,
RMS.pitot_hydrant2, RMS.pitot_pitot2, RMS.pitot_opening2, RMS.pitot_discharge2, RMS.pitot_hydrant3, RMS.pitot_pitot3, RMS.pitot_opening3,
RMS.pitot_discharge3, RMS.pitot_hydrant4, RMS.pitot_pitot4, RMS.pitot_opening4, RMS.pitot_discharge4, RMS.Completed_by, RMS.Test_Date
FROM sdeuser.WATER_HYDRANT INNER JOIN
RMS5SQL.dbo.hydrant_test_VIEW AS RMS ON sdeuser.WATER_HYDRANT.TAG = RMS.TAG

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-31 : 15:34:02

HAVING MAX(Year) = Year

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mikezdman
Starting Member

4 Posts

Posted - 2009-07-31 : 15:34:41

Where does that fit in? Is that put in at the end?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-01 : 08:36:35

So where is the column and which datatype to check for the wanted year?

No, you're never too old to Yak'n'Roll if you're too young to die.

mikezdman
Starting Member

4 Posts

Posted - 2009-08-03 : 11:17:29

This data refers to hydrant testing. I am trying to get the most recent test year for each hydrant. This is coming from the RMS.Test_Year field. Where does the HAVING MAX(Year)=Year fit in the code. I cannot get it to work. Any help is much appreciated as I have hit a wall.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources