Hi Folks
A few weeks ago I had asked how to create a select statement that would only return one record per ID Number where the Timestamp was the latest of many. I received some very good advice (Thank You) from this group.
However, it seems that there are some records with the same timestamp (down to the thousandth of a second) and when this occurs I still get two records for the same ID. If the records were completely identical it would not be a problem (would still return only one record) but one of the fields has a different value, which makes it a unique record.
My statement is below -- how can I change this to get only one record even when the timestamp is exactly the same?
Thanks
Craig
A few weeks ago I had asked how to create a select statement that would only return one record per ID Number where the Timestamp was the latest of many. I received some very good advice (Thank You) from this group.
However, it seems that there are some records with the same timestamp (down to the thousandth of a second) and when this occurs I still get two records for the same ID. If the records were completely identical it would not be a problem (would still return only one record) but one of the fields has a different value, which makes it a unique record.
My statement is below -- how can I change this to get only one record even when the timestamp is exactly the same?
Code:
SELECT DISTINCT MI.Timestamp, MI.ParcelNumber, MI.SSCC18, MI.CustomerNumber, MI.CarrierID,
MI.TransMethod, MI.Hazmat, MI.ExpectedWeight, MI.ActualWeight, ISt.PctVariation, CAST(MI.Lane AS INTEGER) - 1000, MI.ReasonCode, '2' AS WeightStation
FROM whtbSorterProductHistory MI
JOIN (SELECT DISTINCT ParcelNumber ParcelNum, MAX(Timestamp) MaxTime
FROM whtbSorterProductHistory group by ParcelNumber) MaxItem
ON MI.ParcelNumber = MaxItem.ParcelNum AND MI.Timestamp = MaxItem.MaxTime
LEFT OUTER JOIN whtbSorterInductStatus ISt
ON MI.parcelNumber = ISt.ParcelNumber
WHERE MI.SorterName = 'ShippingSorter'
AND ISNUMERIC(ISt.ParcelNumber) = '1'
Thanks
Craig