Return the highest value in a row containing duplicates in access

user_12954220 0
Viewed 43

I just can not seem to find the answer... I have a table

users

name | cat 1 | cat 2 | cat 3 
-------------------------------------------
Karl                        1              
Karl   medium      T        1                        
James  high        T        1                                              
James  medium      T        1                    
James                       1                 
Toni   medium      T        1                       
Toni   high        T        1                  
Toni                        1              
Klara                       1    
Laura                       1                
Thomas                      1                    
Tobi   medium      T        1                                  
Tobi                        1

And I would like the result to be like this: A table which holds a record of each person but only keeps the record for the highest value from cat 1. 

users

name | cat 1 | cat 2 | cat 3 
-------------------------------------------             
Karl   medium      T        1                        
James  high        T        1                                                                                  
Toni   high        T        1                               
Klara                       1    
Laura                       1                
Thomas                      1                    
Tobi   medium      T        1

The nearest I got to a solution was this, but it only returns all the lowest values, when changing MIN to MAX it returns all rows...

SELECT DISTINCT * FROM users
WHERE users.[cat 1] IN
(SELECT MIN([cat 1]) FROM users GROUP BY [name])
ms-access sql
1 Answers

Need a unique identifier field - autonumber should serve. Then use nested TOP N query. Deal with nulls because Null sorts first in ascending order.

If high and medium are the only values, consider:

SELECT * FROM users
WHERE ID IN
(SELECT TOP 1 ID FROM users AS Dupe WHERE Dupe.Name=Users.Name 
    ORDER BY Nz([Cat 1],"ZZ"));

Otherwise, convert text values to numeric:

SELECT * FROM users
WHERE ID IN
(SELECT TOP 1 ID FROM users AS Dupe WHERE Dupe.Name=Users.Name 
    ORDER BY Switch([cat 1]="high",1, [cat 1]="medium",2, [cat 1]="low",3, True,4));
user_76071900
Related