Query Help 1

[jtrembla]

VersionID ForecastID ModifiedDate
867 5 06-Dec-08
868 5 03-Dec-08
869 5 06-Dec-08
870 6 03-Dec-08
871 6 03-Dec-08
873 7 03-Dec-08
875 7 05-Dec-08

I need help writing a query that will return one version per forcastid where a version has the most recent modified date.

I tried using top but it only returns 1 record...

 

[RiverGuy]

SELECT a.*
FROM YourTable a
INNER JOIN
(SELECT ForcecastID, MAX(ModifiedDate) AS MaxDate
FROM YourTable) b
ON a.ModifiedDate = b.MaxDate

 

[RiverGuy]

Oops, forgot the GROUP BY and a join criteria (long day)

SELECT a.*
FROM YourTable a
INNER JOIN
  (SELECT ForcecastID, MAX(ModifiedDate) AS MaxDate
   FROM YourTable GROUP BY ForecastID) b
ON a.ForecastID = b.ForecastID AND a.ModifiedDate = b.MaxDate

 

[jbenson001]

I think you need to add

AND a.ForecastID = b.ForecasetID

 

[jtrembla]

I tried this and I still get multiple forecast ID returned.

SELECT a.*
FROM YourTable a
INNER JOIN
(SELECT ForcecastID, MAX(ModifiedDate) AS MaxDate
FROM YourTable GROUP BY ForecastID) b
ON a.ForecastID = b.ForecastID AND a.ModifiedDate = b.MaxDate

 

[jtrembla]

There is 2 of the same modified dates for multiple versions in a forecast, thus I think I need to pick the max version in my initial select?

 

[RiverGuy]

Yes, also group and join on the max version. Or, if your version correlates to time, you can group on and join max version only.