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

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

BYG
1#
BYG Published in 2018-01-11 21:25:59Z
 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?
Von Aaron
2#
Von Aaron Reply to 2018-01-11 23:35:12Z
 You can use a ROW_NUMBER() to determine the active and none active, example sample data:  DECLARE @T TABLE ( ID INT , RoleID INT , UserID INT , DateAssigned DATETIME , Active INT ) INSERT INTO @T ( ID, RoleID, UserID, DateAssigned, Active ) VALUES ( 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 )  query:  SELECT ID , RoleID , UserID , DateAssigned , Active FROM ( SELECT ID , RoleID , UserID , DateAssigned , Active , ROW_NUMBER() OVER ( PARTITION BY UserID ORDER BY Active DESC ) ctr FROM @T ) t WHERE t.ctr = 1  Result  ID RoleID UserID DateAssigned Active ----------- ----------- ----------- ----------------------- ----------- 1 6 824 2017-11-08 00:00:00.000 1 3 6 833 2017-11-07 00:00:00.000 1 7 6 888 2017-11-11 00:00:00.000 1 8 6 890 2017-11-24 00:00:00.000 0 (4 row(s) affected)  In your case, something like this. (Assuming your current result is based on your select query)  SELECT Id , Name , ACTIVE FROM ( SELECT Id , Name , ACTIVE , ROW_NUMBER() OVER ( PARTITION BY UserID ORDER BY Active DESC ) ctr FROM ( SELECT DISTINCT CONVERT(VARCHAR, p_Empl.ID) AS Id , ( p_empl.Last_Name + p_Empl.First_Name ) AS Name , RoleHistory.Active , RoleHistory.RoleId, UserId 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 ) ) ) T ) TT WHERE tt.ctr = 1 ORDER BY TT.Name 
 You need to login account before you can post.
Processed in 0.302303 second(s) , Gzip On .