I am using SQL Server 2019 and looking for a way to show ONLY the latest value for each AOC based on its latest FW_Version. Here is my query I have so far but this shows everything:
x
SELECT DISTINCT
dbo.Model.ModelName AS AOC,
dbo.Chipset.Chipset,
dbo.FormFactor.FormFactor AS Form_Factor,
dbo.ProductRelease.ECO AS Release,
dbo.ProductRelease.Date AS Release_Date,
dbo.Intel.FWVersion AS FW_Version,
dbo.Intel.ETRACKID
FROM
dbo.Intel
INNER JOIN dbo.ProductRelease ON dbo.Intel.ProductReleaseID = dbo.ProductRelease.ID
INNER JOIN dbo.Model ON dbo.ProductRelease.ModelID = dbo.Model.ID
INNER JOIN dbo.FormFactor ON dbo.Model.FormFactorID = dbo.FormFactor.ID
INNER JOIN dbo.Chipset ON dbo.Intel.ControllerID = dbo.Chipset.ControllerID AND dbo.Intel.ChipsetID = dbo.Chipset.ID
ORDER BY
dbo.Model.ModelName,
dbo.Intel.FWVersion DESC,
dbo.ProductRelease.Date DESC
What I would like to show ONLY items marked in yellow… How can I make this happen?
Here is the list of my Tables which are joined based on their corresponding ID’s
Table 1: Model
ID (Primary Key)
ModelName nvarchar(50)
FormFactorID int
Table 2: FormFactor
ID (Primary Key)
FormFactor nvarchar(15)
Table 3: ProductRelease
ID (Primary Key)
ModelID int
Date date
ECO nvarchar(10)
Table 4: Intel
ID (Primary Key)
ProductReleaseID int
ChipsetID int
FWVersion nvarchar(10)
ETRACKID nvarchar(15)
Table 5: Chipset
ID (Primary Key)
Chipsetnvarchar(20)
Advertisement
Answer
One method uses window functions:
SELECT *
FROM (
SELECT
dbo.Model.ModelName AS AOC,
dbo.Chipset.Chipset,
dbo.FormFactor.FormFactor AS Form_Factor,
dbo.ProductRelease.ECO AS Release,
dbo.ProductRelease.Date AS Release_Date,
dbo.Intel.FWVersion AS FW_Version,
dbo.Intel.ETRACKID,
ROW_NUMBER() OVER(PARTITION BY dbo.Model.ModelName ORDER BY dbo.ProductRelease.Date DESC) rn
FROM
dbo.Intel
INNER JOIN dbo.ProductRelease ON dbo.Intel.ProductReleaseID = dbo.ProductRelease.ID
INNER JOIN dbo.Model ON dbo.ProductRelease.ModelID = dbo.Model.ID
INNER JOIN dbo.FormFactor ON dbo.Model.FormFactorID = dbo.FormFactor.ID
INNER JOIN dbo.Chipset ON dbo.Intel.ControllerID = dbo.Chipset.ControllerID AND dbo.Intel.ChipsetID = dbo.Chipset.ID
) t
WHERE rn = 1
ORDER BY AOC