I just can’t figure out how to do this very simple function after spending about 4 hours on this.
I have a table that has multiple effective dates associated to an ID#. I want a table that will display the ID # and the Name, only for the ID with the most recent effective Date:
ID Column: faclityId
Name Column: Name
Date Column: EffectiveDate
select distinct FName.FacilityId, FName.Name, max(FName.EffectiveDate) as MaxDate
from FacilityName_RV FName
group by FName.FacilityId, FName.Name
This code will show all instances of the FacilityId’s, where as I only want the most current one as per the Effective date. If I remove “Name” from the query, then it works fine, however I need the name.
I thought the following might work, however I don’t know how to properly reference the MaxDate value in my first select statement, from my join statement:
select distinct FName.FacilityId, F3.Name, max(FName.EffectiveDate) as MaxDate
from FacilityName_RV FName
left join
(select F2.FacilityId, F2.Name, F2.EffectiveDate
from FacilityName_RV F2) as F3
on F3.facilityId = FName.facilityId and F3.EffectiveDate = MaxDate
group by FName.FacilityId, F3.Name
Select the max effective date in a sub-query that you compare to the effective date in the main query in your where clause.
select distinct FName.FacilityId, FName.Name, FName.EffectiveDate as MaxDate
from FacilityName_RV FName
where
FName.EffectiveDate=(select max(EffectiveDate) from FacilityName_RV)
This assumes that the effective date column is unique. If it’s not, you’ll need a way to handle multiple rows with the same effective date.
Thanks, but I’m not fully following. The example you gave only finds 1 record in all the data, as it is just looking for the max date in the entire table. I need to find the maxdate for each ID number in the FacilityName table.
this is a well-known problem in SQL called “the row holding the groupwise max” (try that phrase in google) with about a dozen ways to solve it
WITH maxdates AS
( SELECT FacilityId
, MAX(EffectiveDate) as MaxDate
FROM FacilityName_RV
GROUP
BY FacilityId )
SELECT t.FacilityId
, m.MaxDate
, t.Name
FROM FacilityName_RV AS t
INNER
JOIN maxdates AS m
ON m.FacilityId = t.FacilityId
AND m.MaxDate = t.EffectiveDate
edit omigod i forgot the last line, a critical part of the solution
WITH LatestDates AS (
SELECT
FacilityId,
MAX(EffectiveDate) as MaxDate
FROM
FacilityName_RV
GROUP BY
FacilityId
)
SELECT
FName.FacilityId,
FName.Name,
L.MaxDate
FROM
FacilityName_RV FName
INNER JOIN
LatestDates L ON FName.FacilityId = L.FacilityId AND FName.EffectiveDate = L.MaxDate;
First Query - represents yours @r937
Second Query - represents mine posted.
CTE Name:
First Query: maxdates
Second Query: LatestDates
Alias Names:
First Query: Uses t for FacilityName_RV and m for the CTE.
Second Query: Uses FName for FacilityName_RV and L for the CTE.
Order of Selected Columns:
First Query: Selects FacilityId, MaxDate, then Name.
Second Query: Selects FacilityId, Name, then MaxDate.
Given these differences, the two queries are not exact copies of each other. They differ in the naming of CTEs and aliases, as well as the order of the selected columns in the final SELECT statement. These differences don’t affect the result set in terms of the data returned, but they do mean the queries are not identical in their text or structure.