November 2, 2009 at 1:33 pm
SQL SERVER 2005. The table format is as follows.I have 20 fields or columns.
I need to select from SourceSiteId(text GUID) which have mutiple records with the same GUID. The SourceSiteID is the Primary Key. The field TreatmentDate (datetime field) will have the latest date a treatment was applied to the site. I need to select the latest date from each of the SourceSiteID's in the table.
SourceSiteID TreatmentDate Status
04da77bb-4429-4e5a-b0c1-966ab7663be0 10/1/2009 12:45:10 PM Active
04da77bb-4429-4e5a-b0c1-966ab7663be0 10/1/2009 1:03:15 PM Mosquito Fish
04da77bb-4429-4e5a-b0c1-966ab7663be0 7/8/2009 3:04:19 PM Active
This is a GIS application for geospatial locations of locations. I am trying to process the SQl Statements in a Model Builder application. :crazy:
November 2, 2009 at 1:39 pm
Without your code it is a little hard to know what you are having problems with here. Also, for the best help possible, please read and follow the instructions in the first article I reference in my signature block below regarding "Asking for assistance."
Doing that will actually help you be getting tested code in return.
November 2, 2009 at 1:43 pm
Lynn is right, more info would help. In the meantime, this may get you close to what you need:
SELECT t.SourceSiteID,
t.TreatmentDate,
t.Status
FROM myTable t
INNER JOIN (
SELECT SourceSiteID, MAX(TreatmentDate) as LastTreatmentDate
FROM myTable
GROUP BY SourceSiteID
) t2 ON t.SourceSiteID = t2.SourceSiteID AND t.TreatmentDate = t2.LastTreatmentDate
November 2, 2009 at 1:45 pm
Sorry about that I am reading it now and wil post back with a more structured query, no pun intended.
November 2, 2009 at 1:48 pm
croberts 87372 (11/2/2009)
Sorry about that I am reading it now and wil post back with a more structured query, no pun intended.
Not a problem. I noticed you were new here on SSC and thought it would help you now and in the future. Plus, another benefit of following those instructions, more people will jump in and you may get several different solutions to the problem.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply