March 12, 2007 at 11:49 am
Hello,
I have a problem where I'm trying to get a result set of distinct values based on one column, and the MAX value of another.
I have a column called MMS CHAR(10) and one called TrackingDate DATETIME. In the table, I can have one or more instances of an MMS and TrackingDate, or just one. I need for the query to return all of the distinct values of MMS, and just the MAX(TrackingDate) when there is more than one instance of a particular MMS
In the following query;
*******************************
SELECT distinct MMS,
CASE
WHEN COUNT(MMS) > 1 THEN MAX(TrackingDate)
ELSE TrackingDate
END AS CheckTrackingDate
FROM DailyAccountsDownload_Tracking
GROUP BY MMS, TrackingDate
********************************
This is an attempt to do that, but it does not quite work.
If you have any ideas on this, please share.
Thank you for your help!
CSDunn
March 12, 2007 at 12:00 pm
SELECT MMS, MAX(TrackingDate) As CheckTrackingDate
FROM DailyAccountsDownload_Tracking
GROUP BY MMS
Grouping by a column accomplishes the Distinct requirement.
March 12, 2007 at 12:20 pm
That works until I introduce other fields into the query (sorry, I didn't tell you there were other fields)
I found that this will work:
SELECT da.MMS,da.Zip,da.TrackingDate
FROM DailyAccountsDownload_Tracking da
INNER JOIN (SELECT MMS,Max(trackingDate)AS TrackingDate FROM dailyaccountsdownload_tracking
GROUP BY mms) dt ON dt.MMS = da.MMS and dt.TrackingDate = da.TrackingDate
ORDER BY da.MMs
Thank you for your input!
CSDunn
March 12, 2007 at 12:38 pm
Joining to a derived table that generates the MAX() is a common way to solve this, just beware of ties.
If you have 2 records with different Zip values, but same MMS and TrackingDate, you'll still end up with both records in your resultset, because both will satisfy the join to MAX(TrackingDate).
March 13, 2007 at 3:08 am
In this case, you just need to group by all the non-aggregated fields and can dispense with the inner query :
SELECT da.MMS,da.Zip,MAX(da.TrackingDate) AS TrackingDate
FROM DailyAccountsDownload_Tracking da
GROUP BY da.MMS,da.Zip ORDER BY da.MMs
You still risk multiple returns from ties. In that scenario, if you don't care which of the multiples you get, you could do :
SELECT da.MMS,MAX(da.Zip) AS Zip,MAX(da.TrackingDate) AS TrackingDate
FROM DailyAccountsDownload_Tracking da
GROUP BY da.MMS ORDER BY da.MMs
March 15, 2007 at 12:04 pm
I am sure this is not the most elegant solution and...
I solved this with a SP which 1) inserts all my data (with a distinct key and the transaction date) into a table variable, 2) loops through the table in distinct key + descending date order and removes all the successive duplicates for each distinct key, and 3) returns the trimmed data.
Michael
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply