Accroding to your description,I don't understand your requirment clearly.
What is HasItAceess? Which column is group by? I'm guessing that you need to distinct the username.
Just like this:
select DISTINCT UserName,CompanyName from GT
If this isn't match your requirment,you could post more details to us.
Best regards,
Yijing Sun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
actually I want to bring those users who have access to IT role, it doesn't matter if they have access to another role.
if they have access to IT role, then I want to set a flag in my query with true and if they don't have access to IT role, so accordingly the result will be false.
something like this:
select ....
CASE WHEN Group='IT'
THEN 1 ELSE 0 END AS HasITAccess from ...
in this case the result will be:
UserName
CompanyName
HasItAceess
Test1
cp1
1
Test1
cp1
0
Test2
cp2
0
so, I have to remove the second row because Test 1 has access to It role and my requirement will be :
SELECT Username,CompanyName,MAX(HasITAccess) FROM (
SELECT DISTINCT Username,CompanyName,
CASE WHEN GroupName='IT'
THEN 1 ELSE 0 END AS HasITAccess
FROM [Table]) AS result
GROUP BY Username,CompanyName
Member
112 Points
398 Posts
Group by completion
Dec 08, 2020 02:03 PM|vahid.ch|LINK
I have a Table as below:
how can I write a query to get the result :
Participant
1120 Points
291 Posts
Re: Group by completion
Dec 08, 2020 05:53 PM|imapsp|LINK
Why is the result 1 for Test1/cp1 and 0 for Test2/cp2? What is the criteria?
Contributor
3380 Points
1284 Posts
Re: Group by completion
Dec 09, 2020 02:29 AM|yij sun|LINK
Hi vahid.ch,
Accroding to your description,I don't understand your requirment clearly.
What is HasItAceess? Which column is group by? I'm guessing that you need to distinct the username.
Just like this:
select DISTINCT UserName,CompanyName from GTIf this isn't match your requirment,you could post more details to us.
Best regards,
Yijing Sun
Member
112 Points
398 Posts
Re: Group by completion
Dec 09, 2020 07:56 AM|vahid.ch|LINK
thanks for the feedback.
actually I want to bring those users who have access to IT role, it doesn't matter if they have access to another role.
if they have access to IT role, then I want to set a flag in my query with true and if they don't have access to IT role, so accordingly the result will be false.
something like this:
select ....
CASE WHEN Group='IT'
THEN 1 ELSE 0 END AS HasITAccess from ...
in this case the result will be:
so, I have to remove the second row because Test 1 has access to It role and my requirement will be :
Member
112 Points
398 Posts
Re: Group by completion
Dec 09, 2020 10:01 AM|vahid.ch|LINK
I found the solution:
Participant
1120 Points
291 Posts
Re: Group by completion
Dec 09, 2020 02:45 PM|imapsp|LINK
You can leave your query as follows:
Hope this help