SELECT *
FROM Users U
WHERE EXISTS
(
	SELECT *
	FROM Users U1
	GROUP BY U1.UserGroup
	HAVING U.[User_Id] = MAX(U1.[User_Id])
);

WITH MaxUsers
AS
(
	SELECT MAX([User_Id]) AS [User_Id]
	FROM Users
	GROUP BY UserGroup
)
SELECT U.*
FROM Users U
	JOIN MaxUsers M
		ON U.[User_Id] = M.[User_Id];

SELECT U.*
FROM Users U
	CROSS APPLY
	(
		SELECT MAX(U1.[User_Id])
		FROM Users U1
		WHERE U1.UserGroup = U.UserGroup
	) D
WHERE U.[User_Id] = D.[User_Id];