Complex Query (Multiple Tables & Joins) - Need to Restrict to Max Date

Question

Post reply

Complex Query (Multiple Tables & Joins) - Need to Restrict to Max Date

gski

SSC Rookie

Points: 32
More actions
January 8, 2025 at 8:58 pm

#4512241

I am using the query listed below.
USE GAGETRAK_26237
SELECT G.Gage_ID 'ID', G.Model_No 'Model', L.LocationName 'Room', D.GageDescriptionName 'Desc',C.Calibration_DateTime 'Cal', G.Gage_SN 'S/N', S.StatusName 'Status', C.NextDue 'Due'
FROM GAGETRAK.Gages G
JOIN GAGETRAK.Locations L ON G.StorageLocation_RID_FK=L.Location_RID
JOIN GAGETRAK.GageDescriptions D ON G.GageDescription_RID_FK=D.GageDescription_RID
JOIN GAGETRAK.Status S ON G.Status_RID_FK=S.Status_RID AND S.StatusName IN ('Active','RfS', 'Out for Calibration')
JOIN GAGETRAK.Calibrations C ON G.Gage_RID=C.Gage_RID_FK
ORDER BY G.Gage_ID
GO
It returns the results as shown below.
The output is as expected, except ID has multiple rows with different values for Cal and Due. I need to have the query only return the row where the value for Cal is the latest (maximum) and not return the remaining rows for the given ID. For example, rows 109 and 110 differ by the value for Cal (and Due, but I only need to restrict one as these columns work together). I want the query to return only row 110, which has the max value for Cal. I have tried varying combinations of a sub Select statement, but can't get the syntax to work given the multiple Joins.
Any ideas?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18868 More actions · Answer 1

The simple approach would be to put your query in a CTE with

,ROW_NUMBER() OVER (PARTITION BY Model ORDER BY Cal DESC) AS rn

and then SELECT FROM the CTE WHERE rn = 1.

ScottPletcher SSC Guru Points: 101137 More actions · Answer 2

SELECT G.Gage_ID 'ID', G.Model_No 'Model', L.LocationName 'Room', D.GageDescriptionName 'Desc',C.Calibration_DateTime 'Cal', G.Gage_SN 'S/N', S.StatusName 'Status', C.NextDue 'Due'

FROM GAGETRAK.Gages G

JOIN GAGETRAK.Locations L ON G.StorageLocation_RID_FK=L.Location_RID

JOIN GAGETRAK.GageDescriptions D ON G.GageDescription_RID_FK=D.GageDescription_RID

JOIN GAGETRAK.Status S ON G.Status_RID_FK=S.Status_RID AND S.StatusName IN ('Active','RfS', 'Out for Calibration')

CROSS APPLY (

SELECT TOP (1) *

FROM GAGETRAK.Calibrations C

WHERE G.Gage_RID=C.Gage_RID_FK

ORDER BY C.NextDue DESC

) AS C

ORDER BY G.Gage_ID

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".