camaroracer016 camaroracer016 - 10 months ago 52
SQL Question

Access SQL Distinct max value

Been stumbling over this for hours now. I've looked at several other articles on here but cant find one that fits my needs. Have an Access database where i am attempting to remove possible duplicates and also get the max values for a unique list. I have a few fields with needed data:


CustomerNumber EmpID SurveyResultID SurveyTotalScore SurveyQuestionCount


CustomerNumber has duplicates but i need distinct values only. If there is a duplicate CustomerNumber with different SurveyTotalScore, i need the MAX score.

Essentially i want to return a list of distinct CustomerNumber with the highest SurveyTotalScore and no duplicates. What's the best way?

Update:
Here is one query i've tried. It works if i just leave CustomerNumber by itself but i need the EmpID and some other fields.

SELECT CustomerNumber, Max(SurveyTotalScore) AS MaxScore, SurveyResultID
FROM CSATDetail
GROUP BY CustomerNumber, SurveyResultID;



Example Data:


+----------------+----------+----------------+-------+
| CustomerNumber | MaxScore | SurveyResultID | EmpID |
+----------------+----------+----------------+-------+
| 259 | 40 | 461500 | 83 |
| 259 | 38 | 461501 | 83 |
| 695 | 40 | 461502 | 59 |
| 695 | 40 | 461504 | 59 |
| 734 | 40 | 461503 | 96 |
+----------------+----------+----------------+-------+


What i'd like to have come out is this. It selects the maxscore and doesn't have duplicates if there is more than one result for an EmpID

+----------------+----------+----------------+-------+
| CustomerNumber | MaxScore | SurveyResultID | EmpID |
+----------------+----------+----------------+-------+
| 259 | 40 | 461500 | 83 |
| 695 | 40 | 461502 | 59 |
| 734 | 40 | 461503 | 96 |
+----------------+----------+----------------+-------+

Answer Source

You're one step away.

  • Generate a set with the max total and customerID (looks like you don't need/want surveyID in that). (check, with a little tweak)
  • Then join it back to the base set to get the info you need. This limits the results of the full set of data by those with just the max total allowing you to get the other needed info. (Just need this!)

.

SELECT CD.CustomerNumber, MaxScore, min(SurveyResultID), EmpID 
FROM CSATDETAIL CD
INNER JOIN (SELECT CustomerNumber, Max(SurveyTotalScore) AS MaxScore
            FROM CSATDetail CI
            GROUP BY CustomerNumber) CD2
  on CD.CustomerNumber = CD2.customerNumber
 and CD.SurveyTotalScore = CD2.MaxScore
GROUP BY CD.CustomerNumber, MaxScore, EmpID 

and since it appears you can have ties based on sample data and you want the min survey..... we needed a group by and min.

Alternative approaches involve using exists or in and using coloration; but i'm not sure access can support those methods: So i stuck with the tried-and-true approach.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download