CODEWITHSUNDEEP
sqlsql-serversql-server-2008
amart47
amart47

Reputation: 95

Max() not filtering out MIN()

when I use run the query below, it returns duplicate StockNo's because some of them have duplicate WorkInProgress codes (FiWipStatus Code). Is there a way to exclude the record based on the the MIN() on rowlastupdated? as always, appreciate any help!

SELECT     dbo.InventoryVehicle.StockNo, dbo.VehicleSales.FiWipStatusCode, 
MAX(dbo.VehicleSales.RowLastUpdated) AS Expr1
FROM         dbo.VehicleSales RIGHT OUTER JOIN
dbo.InventoryVehicle ON dbo.VehicleSales.StockNo = dbo.InventoryVehicle.StockNo
GROUP BY dbo.InventoryVehicle.StockNo, dbo.VehicleSales.FiWipStatusCode, 
dbo.VehicleSales.RowLastUpdated

Upvotes: 0

Views: 51

Answers (1)

iSR5
iSR5

Reputation: 3498

If I got it correctly, you need to get the records based on their last update date and time (which is RowLastUpdated). if so, you can do something like this : 

SELECT 
    iv.StockNo
,   vs.FiWipStatusCode
,   vs.RowLastUpdated 
FROM (
SELECT     
    iv.StockNo
,   vs.FiWipStatusCode
,   vs.RowLastUpdated 
,   ROW_NUMBER() OVER(PARTITION BY iv.StockNo ORDER BY vs.RowLastUpdated DESC) AS RN
FROM         
    VehicleSales vs
LEFT JOIN  InventoryVehicle iv ON vs.StockNo = iv.StockNo   
) D 
WHERE 
    RN = 1

where ROW_NUMBER() will number the rows based on StockNo and order them based on RowLastUpdated in DESC. So, the first row of each distinct StockNo will be the MAX() datetime in your aggregation query. if you want to get the MIN() just change the order to ASC

Upvotes: 2

Related Questions