Home How to get only one result from each group based on condition
Reply: 0

How to get only one result from each group based on condition

user2200
1#
user2200 Published in July 18, 2018, 10:30 pm

I have a roleHistory table where I keep track of the active and inactive roles for all the employees. This is my table:

Id RoleId UserId  DateAssigned    Active 
-----------------------------------------
 1    6    824     2017-11-08       1   
 2    7    824     2017-11-08       0     
 3    6    833     2017-11-07       1    
 4    7    833     2017-11-07       0
 5    6    888     2017-11-07       0
 6    7    888     2017-11-10       0
 7    6    888     2017-11-11       1
 8    6    890     2017-11-24       0

I want to group it by userId and roleId and if for example userId 824 is active in one role and inactive in another role, I only want to get the active one. If the user is inactive in both roles, I want to get inactive.

This is what I tried so far:

 SELECT DISTINCT 
     CONVERT(varchar, p_Empl.ID) AS Id, 
     (p_empl.Last_Name + p_Empl.First_Name) AS Name, 
     RoleHistory.Active, RoleHistory.RoleId
 FROM  
     p_Empl
 INNER JOIN 
     UserProfile ON UserProfile.EmployeeID = p_Empl.ID
 INNER JOIN 
     RoleHistory ON RoleHistory.UserId = UserProfile.Id
 INNER JOIN 
     webpages_Roles ON Webpages_Roles.RoleID = RoleHistory.RoleID
 WHERE 
     (webpages_Roles.RoleName = 'Project Manager' OR 
      webpages_Roles.RoleName = 'Sales Manager') 
     AND (RoleHistory.Id IN (SELECT MAX(RoleHistory.Id) AS DateUpdated 
                             FROM RoleHistory 
                             GROUP BY UserId,RoleId))  
ORDER BY 
    Name   

This is the results this query returns:

 1    6    824     2017-11-08       1   
 2    7    824     2017-11-08       0     
 3    6    833     2017-11-07       1    
 4    7    833     2017-11-07       0
 6    7    888     2017-11-10       0
 7    6    888     2017-11-11       1
 8    6    890     2017-11-24       0

But what I want to accomplish is this:

 1    6    824     2017-11-08       1     
 3    6    833     2017-11-07       1    
 7    6    888     2017-11-11       1
 8    6    890     2017-11-24       0

Can anyone help me to achieve that?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.313722 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO