Group By and list not matched
up vote
0
down vote
favorite
I have table as under:
Col1 Col2 Col3 Col4 Col5
1 50.9499411799115 Point imp A
1 109.69487431133 Point exp 1
1 107.69487431133 Point exp 2
1 1019.69487431133 Point exp B
2 51.5403193833315 Point imp 0
2 50.5403193833315 Point exp 3
I want to group by Col1 and select all the ones where there are no 'A' or 'B' in Col5
I used the below query for generating the ouput in MSSQL but didnt get correct result, can someone point out my mistake
SELECT Col1
FROM table1
WHERE
Col5 NOT LIKE('%A%')
or Col5 NOT LIKE('%B%')
GROUP BY Col1;
Therefore my output should be
Col1
2
sql-server group-by asked Nov 5 at 2:07
Duffer
478
|
show 1 more comment
up vote
0
down vote
favorite
I have table as under:
Col1 Col2 Col3 Col4 Col5
1 50.9499411799115 Point imp A
1 109.69487431133 Point exp 1
1 107.69487431133 Point exp 2
1 1019.69487431133 Point exp B
2 51.5403193833315 Point imp 0
2 50.5403193833315 Point exp 3
I want to group by Col1 and select all the ones where there are no 'A' or 'B' in Col5
I used the below query for generating the ouput in MSSQL but didnt get correct result, can someone point out my mistake
SELECT Col1
FROM table1
WHERE
Col5 NOT LIKE('%A%')
or Col5 NOT LIKE('%B%')
GROUP BY Col1;
Therefore my output should be
Col1
2
asked Nov 5 at 2:07
Duffer
478
Should beANDnotORshouldn't it? i.e. its not like A AND its not like B?
– Dale Burrell
Nov 5 at 2:08
@DaleBurrell if I use and I get empty result however it should display Col2
– Duffer
Nov 5 at 2:11
You are also missing aNOTlike B. But you have 3 rows where Col1=1 where Col5 is not A or B, so you will get both 1 & 2 in your result set.
– Dale Burrell
Nov 5 at 2:18
@Duffer: But if your Col5 having "A" & "B" as value then why are you trying for LIKE operator. Simply use Col5 NOT IN ('A','B')
– im_one
Nov 5 at 2:20
1
There are a number of ways you can do this... For example, you can useNOT EXISTSorEXCEPTor a left self join where null... As a quick example:SELECT Col1 FROM table1 AS T1 WHERE NOT EXISTS (SELECT 1 FROM table1 AS T2 WHERE T2.Col1 = T1.Col1 AND T2.Col5 LIKE '%[AB]%') GROUP BY Col1;
– ZLK
Nov 5 at 2:21
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have table as under:
Col1 Col2 Col3 Col4 Col5
1 50.9499411799115 Point imp A
1 109.69487431133 Point exp 1
1 107.69487431133 Point exp 2
1 1019.69487431133 Point exp B
2 51.5403193833315 Point imp 0
2 50.5403193833315 Point exp 3
I want to group by Col1 and select all the ones where there are no 'A' or 'B' in Col5
I used the below query for generating the ouput in MSSQL but didnt get correct result, can someone point out my mistake
SELECT Col1
FROM table1
WHERE
Col5 NOT LIKE('%A%')
or Col5 NOT LIKE('%B%')
GROUP BY Col1;
Therefore my output should be
Col1
2
asked Nov 5 at 2:07
Duffer
478
I have table as under:
Col1 Col2 Col3 Col4 Col5
1 50.9499411799115 Point imp A
1 109.69487431133 Point exp 1
1 107.69487431133 Point exp 2
1 1019.69487431133 Point exp B
2 51.5403193833315 Point imp 0
2 50.5403193833315 Point exp 3
I want to group by Col1 and select all the ones where there are no 'A' or 'B' in Col5
I used the below query for generating the ouput in MSSQL but didnt get correct result, can someone point out my mistake
SELECT Col1
FROM table1
WHERE
Col5 NOT LIKE('%A%')
or Col5 NOT LIKE('%B%')
GROUP BY Col1;
Therefore my output should be
Col1
2
asked Nov 5 at 2:07
Duffer
478
asked Nov 5 at 2:07
Duffer
478
edited Nov 5 at 2:21
asked Nov 5 at 2:07
Duffer
478
asked Nov 5 at 2:07
Duffer
478
asked Nov 5 at 2:07
Duffer
478
478
Should beANDnotORshouldn't it? i.e. its not like A AND its not like B?
– Dale Burrell
Nov 5 at 2:08
@DaleBurrell if I use and I get empty result however it should display Col2
– Duffer
Nov 5 at 2:11
You are also missing aNOTlike B. But you have 3 rows where Col1=1 where Col5 is not A or B, so you will get both 1 & 2 in your result set.
– Dale Burrell
Nov 5 at 2:18
@Duffer: But if your Col5 having "A" & "B" as value then why are you trying for LIKE operator. Simply use Col5 NOT IN ('A','B')
– im_one
Nov 5 at 2:20
1
There are a number of ways you can do this... For example, you can useNOT EXISTSorEXCEPTor a left self join where null... As a quick example:SELECT Col1 FROM table1 AS T1 WHERE NOT EXISTS (SELECT 1 FROM table1 AS T2 WHERE T2.Col1 = T1.Col1 AND T2.Col5 LIKE '%[AB]%') GROUP BY Col1;
– ZLK
Nov 5 at 2:21
|
show 1 more comment
Should beANDnotORshouldn't it? i.e. its not like A AND its not like B?
– Dale Burrell
Nov 5 at 2:08
@DaleBurrell if I use and I get empty result however it should display Col2
– Duffer
Nov 5 at 2:11
You are also missing aNOTlike B. But you have 3 rows where Col1=1 where Col5 is not A or B, so you will get both 1 & 2 in your result set.
– Dale Burrell
Nov 5 at 2:18
@Duffer: But if your Col5 having "A" & "B" as value then why are you trying for LIKE operator. Simply use Col5 NOT IN ('A','B')
– im_one
Nov 5 at 2:20
1
There are a number of ways you can do this... For example, you can useNOT EXISTSorEXCEPTor a left self join where null... As a quick example:SELECT Col1 FROM table1 AS T1 WHERE NOT EXISTS (SELECT 1 FROM table1 AS T2 WHERE T2.Col1 = T1.Col1 AND T2.Col5 LIKE '%[AB]%') GROUP BY Col1;
– ZLK
Nov 5 at 2:21
Should be
AND not OR shouldn't it? i.e. its not like A AND its not like B?– Dale Burrell
Nov 5 at 2:08
Should be
AND not OR shouldn't it? i.e. its not like A AND its not like B?– Dale Burrell
Nov 5 at 2:08
@DaleBurrell if I use and I get empty result however it should display Col2
– Duffer
Nov 5 at 2:11
@DaleBurrell if I use and I get empty result however it should display Col2
– Duffer
Nov 5 at 2:11
You are also missing a
NOT like B. But you have 3 rows where Col1=1 where Col5 is not A or B, so you will get both 1 & 2 in your result set.– Dale Burrell
Nov 5 at 2:18
You are also missing a
NOT like B. But you have 3 rows where Col1=1 where Col5 is not A or B, so you will get both 1 & 2 in your result set.– Dale Burrell
Nov 5 at 2:18
@Duffer: But if your Col5 having "A" & "B" as value then why are you trying for LIKE operator. Simply use Col5 NOT IN ('A','B')
– im_one
Nov 5 at 2:20
@Duffer: But if your Col5 having "A" & "B" as value then why are you trying for LIKE operator. Simply use Col5 NOT IN ('A','B')
– im_one
Nov 5 at 2:20
1
1
There are a number of ways you can do this... For example, you can use
NOT EXISTS or EXCEPT or a left self join where null... As a quick example: SELECT Col1 FROM table1 AS T1 WHERE NOT EXISTS (SELECT 1 FROM table1 AS T2 WHERE T2.Col1 = T1.Col1 AND T2.Col5 LIKE '%[AB]%') GROUP BY Col1;– ZLK
Nov 5 at 2:21
There are a number of ways you can do this... For example, you can use
NOT EXISTS or EXCEPT or a left self join where null... As a quick example: SELECT Col1 FROM table1 AS T1 WHERE NOT EXISTS (SELECT 1 FROM table1 AS T2 WHERE T2.Col1 = T1.Col1 AND T2.Col5 LIKE '%[AB]%') GROUP BY Col1;– ZLK
Nov 5 at 2:21
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
The problem is you're trying to eliminate one row based on data in another, so the only way to do that is to check the other day e.g.
select Col1
from table1 D1
where not exists (select 1 from table1 D2 where D2.Col1 = D1.Col1 and (Col5 like ('%A%') or Col5 like ('%B%')))
group by Col1
answered Nov 5 at 2:32
Dale Burrell
1,69011841
Thanks it worked, specially the '(' in where clause worked :)
– Duffer
Nov 5 at 2:45
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
The problem is you're trying to eliminate one row based on data in another, so the only way to do that is to check the other day e.g.
select Col1
from table1 D1
where not exists (select 1 from table1 D2 where D2.Col1 = D1.Col1 and (Col5 like ('%A%') or Col5 like ('%B%')))
group by Col1
answered Nov 5 at 2:32
Dale Burrell
1,69011841
Thanks it worked, specially the '(' in where clause worked :)
– Duffer
Nov 5 at 2:45
add a comment |
up vote
1
down vote
accepted
The problem is you're trying to eliminate one row based on data in another, so the only way to do that is to check the other day e.g.
select Col1
from table1 D1
where not exists (select 1 from table1 D2 where D2.Col1 = D1.Col1 and (Col5 like ('%A%') or Col5 like ('%B%')))
group by Col1
answered Nov 5 at 2:32
Dale Burrell
1,69011841
Thanks it worked, specially the '(' in where clause worked :)
– Duffer
Nov 5 at 2:45
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
The problem is you're trying to eliminate one row based on data in another, so the only way to do that is to check the other day e.g.
select Col1
from table1 D1
where not exists (select 1 from table1 D2 where D2.Col1 = D1.Col1 and (Col5 like ('%A%') or Col5 like ('%B%')))
group by Col1
answered Nov 5 at 2:32
Dale Burrell
1,69011841
The problem is you're trying to eliminate one row based on data in another, so the only way to do that is to check the other day e.g.
select Col1
from table1 D1
where not exists (select 1 from table1 D2 where D2.Col1 = D1.Col1 and (Col5 like ('%A%') or Col5 like ('%B%')))
group by Col1
answered Nov 5 at 2:32
Dale Burrell
1,69011841
answered Nov 5 at 2:32
Dale Burrell
1,69011841
answered Nov 5 at 2:32
Dale Burrell
1,69011841
answered Nov 5 at 2:32
Dale Burrell
1,69011841
1,69011841
Thanks it worked, specially the '(' in where clause worked :)
– Duffer
Nov 5 at 2:45
add a comment |
Thanks it worked, specially the '(' in where clause worked :)
– Duffer
Nov 5 at 2:45
Thanks it worked, specially the '(' in where clause worked :)
– Duffer
Nov 5 at 2:45
Thanks it worked, specially the '(' in where clause worked :)
– Duffer
Nov 5 at 2:45
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53147418%2fgroup-by-and-list-not-matched%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Should be
ANDnotORshouldn't it? i.e. its not like A AND its not like B?– Dale Burrell
Nov 5 at 2:08
@DaleBurrell if I use and I get empty result however it should display Col2
– Duffer
Nov 5 at 2:11
You are also missing a
NOTlike B. But you have 3 rows where Col1=1 where Col5 is not A or B, so you will get both 1 & 2 in your result set.– Dale Burrell
Nov 5 at 2:18
@Duffer: But if your Col5 having "A" & "B" as value then why are you trying for LIKE operator. Simply use Col5 NOT IN ('A','B')
– im_one
Nov 5 at 2:20
1
There are a number of ways you can do this... For example, you can use
NOT EXISTSorEXCEPTor a left self join where null... As a quick example:SELECT Col1 FROM table1 AS T1 WHERE NOT EXISTS (SELECT 1 FROM table1 AS T2 WHERE T2.Col1 = T1.Col1 AND T2.Col5 LIKE '%[AB]%') GROUP BY Col1;– ZLK
Nov 5 at 2:21