How to compare column values/rows in same table
It should pull the count of users_id where for the firstname/lastname of users is different for each of the user id.
here it should result 2 records( 1 & 7).
sql
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
add a comment |
It should pull the count of users_id where for the firstname/lastname of users is different for each of the user id.
here it should result 2 records( 1 & 7).
sql
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
Table details are in the hyper link, Pls let me know if anyone has come across such scenario to pull the records.
– Radha Jogendra
Nov 21 '18 at 20:30
1
What have you tried so far?
– lurker
Nov 21 '18 at 20:56
No link, please. Add sample table data and the expected result as properly formatted text. Also show us your current query attempt. BTW, are you using MySQL or DB2?
– jarlh
Nov 21 '18 at 20:58
What if firstname values are same but lastname values are different ? Will that still be considered a different row ?
– Madhur Bhaiya
Nov 21 '18 at 21:03
1
Mysql and db2 are two different products with different syntax and features. Therefore I removed the conflicting product tags. Pls add the one back that you really use.
– Shadow
Nov 21 '18 at 21:23
add a comment |
It should pull the count of users_id where for the firstname/lastname of users is different for each of the user id.
here it should result 2 records( 1 & 7).
sql
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
It should pull the count of users_id where for the firstname/lastname of users is different for each of the user id.
here it should result 2 records( 1 & 7).
sql
sql
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
edited Nov 21 '18 at 21:22
Shadow
25.7k92844
25.7k92844
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
asked Nov 21 '18 at 20:23
Radha JogendraRadha Jogendra
12
12
Table details are in the hyper link, Pls let me know if anyone has come across such scenario to pull the records.
– Radha Jogendra
Nov 21 '18 at 20:30
1
What have you tried so far?
– lurker
Nov 21 '18 at 20:56
No link, please. Add sample table data and the expected result as properly formatted text. Also show us your current query attempt. BTW, are you using MySQL or DB2?
– jarlh
Nov 21 '18 at 20:58
What if firstname values are same but lastname values are different ? Will that still be considered a different row ?
– Madhur Bhaiya
Nov 21 '18 at 21:03
1
Mysql and db2 are two different products with different syntax and features. Therefore I removed the conflicting product tags. Pls add the one back that you really use.
– Shadow
Nov 21 '18 at 21:23
add a comment |
Table details are in the hyper link, Pls let me know if anyone has come across such scenario to pull the records.
– Radha Jogendra
Nov 21 '18 at 20:30
1
What have you tried so far?
– lurker
Nov 21 '18 at 20:56
No link, please. Add sample table data and the expected result as properly formatted text. Also show us your current query attempt. BTW, are you using MySQL or DB2?
– jarlh
Nov 21 '18 at 20:58
What if firstname values are same but lastname values are different ? Will that still be considered a different row ?
– Madhur Bhaiya
Nov 21 '18 at 21:03
1
Mysql and db2 are two different products with different syntax and features. Therefore I removed the conflicting product tags. Pls add the one back that you really use.
– Shadow
Nov 21 '18 at 21:23
Table details are in the hyper link, Pls let me know if anyone has come across such scenario to pull the records.
– Radha Jogendra
Nov 21 '18 at 20:30
Table details are in the hyper link, Pls let me know if anyone has come across such scenario to pull the records.
– Radha Jogendra
Nov 21 '18 at 20:30
1
1
What have you tried so far?
– lurker
Nov 21 '18 at 20:56
What have you tried so far?
– lurker
Nov 21 '18 at 20:56
No link, please. Add sample table data and the expected result as properly formatted text. Also show us your current query attempt. BTW, are you using MySQL or DB2?
– jarlh
Nov 21 '18 at 20:58
No link, please. Add sample table data and the expected result as properly formatted text. Also show us your current query attempt. BTW, are you using MySQL or DB2?
– jarlh
Nov 21 '18 at 20:58
What if firstname values are same but lastname values are different ? Will that still be considered a different row ?
– Madhur Bhaiya
Nov 21 '18 at 21:03
What if firstname values are same but lastname values are different ? Will that still be considered a different row ?
– Madhur Bhaiya
Nov 21 '18 at 21:03
1
1
Mysql and db2 are two different products with different syntax and features. Therefore I removed the conflicting product tags. Pls add the one back that you really use.
– Shadow
Nov 21 '18 at 21:23
Mysql and db2 are two different products with different syntax and features. Therefore I removed the conflicting product tags. Pls add the one back that you really use.
– Shadow
Nov 21 '18 at 21:23
add a comment |
3 Answers
3
active
oldest
votes
I believe you want:
select user_id
from t
group by user_id
having count(*) = count(distinct firstname) and
count(*) = count(distinct lastname);
I'm not sure if you want the names as pair. If so:
having count(*) = count(distinct firstname, lastname)
Not all databases support multiple arguments to count(distinct). If this is what you really intend, it is easy enough to phrase without using this.
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
Nice approach - I only can hardly imagine he does not want the the name as pair. In Db2 (LUW) the count(distinct firstname, lastname) needs to be changed to count(distinct firstname || lastname) I think
– MichaelTiefenbacher
Nov 23 '18 at 17:18
add a comment |
Following SQL will work - it will also work if you would add lets say this row to your sample data (8,7, 'will', 'Rj') making User_id 7 having 4 rows but two of them with identical names.
with temp as (
SELECT user_id, firstname, lastname
, count(*) over (partition by user_id, firstname, lastname) as counter
FROM t2
)
SELECT * --distinct user_id
FROM temp t
WHERE not exists (SELECT 1 FROM temp WHEE counter = 2 and user_id = t.user_id)
The statements returns all rows with names unless you change the * to the commented distinct user_id then it will only return the user_id having ONLY unique names.
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
add a comment |
select user_id,count(*)
from table
group by user_id having
count(distinct firstname) =count(firstname) and count(distinct
lastname)=count(lastname) ;
This gives those userids where on grouping userids if distinct fname and lname are greater than 1 for each userid group that means that group contains no duplicates.
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
Sorry but your statement will not work if an additional row with sample data (8,7, 'will', 'Rj') would be added.
– MichaelTiefenbacher
Nov 21 '18 at 22:40
I have edited can you please check it now
– Himanshu Ahuja
Nov 22 '18 at 9:23
Ok looks good to me :-)
– MichaelTiefenbacher
Nov 23 '18 at 17:15
If you could please upvote it :)
– Himanshu Ahuja
Nov 23 '18 at 18:33
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53419956%2fhow-to-compare-column-values-rows-in-same-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
I believe you want:
select user_id
from t
group by user_id
having count(*) = count(distinct firstname) and
count(*) = count(distinct lastname);
I'm not sure if you want the names as pair. If so:
having count(*) = count(distinct firstname, lastname)
Not all databases support multiple arguments to count(distinct). If this is what you really intend, it is easy enough to phrase without using this.
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
Nice approach - I only can hardly imagine he does not want the the name as pair. In Db2 (LUW) the count(distinct firstname, lastname) needs to be changed to count(distinct firstname || lastname) I think
– MichaelTiefenbacher
Nov 23 '18 at 17:18
add a comment |
I believe you want:
select user_id
from t
group by user_id
having count(*) = count(distinct firstname) and
count(*) = count(distinct lastname);
I'm not sure if you want the names as pair. If so:
having count(*) = count(distinct firstname, lastname)
Not all databases support multiple arguments to count(distinct). If this is what you really intend, it is easy enough to phrase without using this.
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
Nice approach - I only can hardly imagine he does not want the the name as pair. In Db2 (LUW) the count(distinct firstname, lastname) needs to be changed to count(distinct firstname || lastname) I think
– MichaelTiefenbacher
Nov 23 '18 at 17:18
add a comment |
I believe you want:
select user_id
from t
group by user_id
having count(*) = count(distinct firstname) and
count(*) = count(distinct lastname);
I'm not sure if you want the names as pair. If so:
having count(*) = count(distinct firstname, lastname)
Not all databases support multiple arguments to count(distinct). If this is what you really intend, it is easy enough to phrase without using this.
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
I believe you want:
select user_id
from t
group by user_id
having count(*) = count(distinct firstname) and
count(*) = count(distinct lastname);
I'm not sure if you want the names as pair. If so:
having count(*) = count(distinct firstname, lastname)
Not all databases support multiple arguments to count(distinct). If this is what you really intend, it is easy enough to phrase without using this.
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
answered Nov 21 '18 at 22:45
Gordon LinoffGordon Linoff
763k35296400
763k35296400
Nice approach - I only can hardly imagine he does not want the the name as pair. In Db2 (LUW) the count(distinct firstname, lastname) needs to be changed to count(distinct firstname || lastname) I think
– MichaelTiefenbacher
Nov 23 '18 at 17:18
add a comment |
Nice approach - I only can hardly imagine he does not want the the name as pair. In Db2 (LUW) the count(distinct firstname, lastname) needs to be changed to count(distinct firstname || lastname) I think
– MichaelTiefenbacher
Nov 23 '18 at 17:18
Nice approach - I only can hardly imagine he does not want the the name as pair. In Db2 (LUW) the count(distinct firstname, lastname) needs to be changed to count(distinct firstname || lastname) I think
– MichaelTiefenbacher
Nov 23 '18 at 17:18
Nice approach - I only can hardly imagine he does not want the the name as pair. In Db2 (LUW) the count(distinct firstname, lastname) needs to be changed to count(distinct firstname || lastname) I think
– MichaelTiefenbacher
Nov 23 '18 at 17:18
add a comment |
Following SQL will work - it will also work if you would add lets say this row to your sample data (8,7, 'will', 'Rj') making User_id 7 having 4 rows but two of them with identical names.
with temp as (
SELECT user_id, firstname, lastname
, count(*) over (partition by user_id, firstname, lastname) as counter
FROM t2
)
SELECT * --distinct user_id
FROM temp t
WHERE not exists (SELECT 1 FROM temp WHEE counter = 2 and user_id = t.user_id)
The statements returns all rows with names unless you change the * to the commented distinct user_id then it will only return the user_id having ONLY unique names.
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
add a comment |
Following SQL will work - it will also work if you would add lets say this row to your sample data (8,7, 'will', 'Rj') making User_id 7 having 4 rows but two of them with identical names.
with temp as (
SELECT user_id, firstname, lastname
, count(*) over (partition by user_id, firstname, lastname) as counter
FROM t2
)
SELECT * --distinct user_id
FROM temp t
WHERE not exists (SELECT 1 FROM temp WHEE counter = 2 and user_id = t.user_id)
The statements returns all rows with names unless you change the * to the commented distinct user_id then it will only return the user_id having ONLY unique names.
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
add a comment |
Following SQL will work - it will also work if you would add lets say this row to your sample data (8,7, 'will', 'Rj') making User_id 7 having 4 rows but two of them with identical names.
with temp as (
SELECT user_id, firstname, lastname
, count(*) over (partition by user_id, firstname, lastname) as counter
FROM t2
)
SELECT * --distinct user_id
FROM temp t
WHERE not exists (SELECT 1 FROM temp WHEE counter = 2 and user_id = t.user_id)
The statements returns all rows with names unless you change the * to the commented distinct user_id then it will only return the user_id having ONLY unique names.
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
Following SQL will work - it will also work if you would add lets say this row to your sample data (8,7, 'will', 'Rj') making User_id 7 having 4 rows but two of them with identical names.
with temp as (
SELECT user_id, firstname, lastname
, count(*) over (partition by user_id, firstname, lastname) as counter
FROM t2
)
SELECT * --distinct user_id
FROM temp t
WHERE not exists (SELECT 1 FROM temp WHEE counter = 2 and user_id = t.user_id)
The statements returns all rows with names unless you change the * to the commented distinct user_id then it will only return the user_id having ONLY unique names.
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
answered Nov 21 '18 at 22:38
MichaelTiefenbacherMichaelTiefenbacher
2,1992614
2,1992614
add a comment |
add a comment |
select user_id,count(*)
from table
group by user_id having
count(distinct firstname) =count(firstname) and count(distinct
lastname)=count(lastname) ;
This gives those userids where on grouping userids if distinct fname and lname are greater than 1 for each userid group that means that group contains no duplicates.
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
Sorry but your statement will not work if an additional row with sample data (8,7, 'will', 'Rj') would be added.
– MichaelTiefenbacher
Nov 21 '18 at 22:40
I have edited can you please check it now
– Himanshu Ahuja
Nov 22 '18 at 9:23
Ok looks good to me :-)
– MichaelTiefenbacher
Nov 23 '18 at 17:15
If you could please upvote it :)
– Himanshu Ahuja
Nov 23 '18 at 18:33
add a comment |
select user_id,count(*)
from table
group by user_id having
count(distinct firstname) =count(firstname) and count(distinct
lastname)=count(lastname) ;
This gives those userids where on grouping userids if distinct fname and lname are greater than 1 for each userid group that means that group contains no duplicates.
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
Sorry but your statement will not work if an additional row with sample data (8,7, 'will', 'Rj') would be added.
– MichaelTiefenbacher
Nov 21 '18 at 22:40
I have edited can you please check it now
– Himanshu Ahuja
Nov 22 '18 at 9:23
Ok looks good to me :-)
– MichaelTiefenbacher
Nov 23 '18 at 17:15
If you could please upvote it :)
– Himanshu Ahuja
Nov 23 '18 at 18:33
add a comment |
select user_id,count(*)
from table
group by user_id having
count(distinct firstname) =count(firstname) and count(distinct
lastname)=count(lastname) ;
This gives those userids where on grouping userids if distinct fname and lname are greater than 1 for each userid group that means that group contains no duplicates.
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
select user_id,count(*)
from table
group by user_id having
count(distinct firstname) =count(firstname) and count(distinct
lastname)=count(lastname) ;
This gives those userids where on grouping userids if distinct fname and lname are greater than 1 for each userid group that means that group contains no duplicates.
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
edited Nov 22 '18 at 9:22
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
answered Nov 21 '18 at 20:58
Himanshu AhujaHimanshu Ahuja
658216
658216
Sorry but your statement will not work if an additional row with sample data (8,7, 'will', 'Rj') would be added.
– MichaelTiefenbacher
Nov 21 '18 at 22:40
I have edited can you please check it now
– Himanshu Ahuja
Nov 22 '18 at 9:23
Ok looks good to me :-)
– MichaelTiefenbacher
Nov 23 '18 at 17:15
If you could please upvote it :)
– Himanshu Ahuja
Nov 23 '18 at 18:33
add a comment |
Sorry but your statement will not work if an additional row with sample data (8,7, 'will', 'Rj') would be added.
– MichaelTiefenbacher
Nov 21 '18 at 22:40
I have edited can you please check it now
– Himanshu Ahuja
Nov 22 '18 at 9:23
Ok looks good to me :-)
– MichaelTiefenbacher
Nov 23 '18 at 17:15
If you could please upvote it :)
– Himanshu Ahuja
Nov 23 '18 at 18:33
Sorry but your statement will not work if an additional row with sample data (8,7, 'will', 'Rj') would be added.
– MichaelTiefenbacher
Nov 21 '18 at 22:40
Sorry but your statement will not work if an additional row with sample data (8,7, 'will', 'Rj') would be added.
– MichaelTiefenbacher
Nov 21 '18 at 22:40
I have edited can you please check it now
– Himanshu Ahuja
Nov 22 '18 at 9:23
I have edited can you please check it now
– Himanshu Ahuja
Nov 22 '18 at 9:23
Ok looks good to me :-)
– MichaelTiefenbacher
Nov 23 '18 at 17:15
Ok looks good to me :-)
– MichaelTiefenbacher
Nov 23 '18 at 17:15
If you could please upvote it :)
– Himanshu Ahuja
Nov 23 '18 at 18:33
If you could please upvote it :)
– Himanshu Ahuja
Nov 23 '18 at 18:33
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53419956%2fhow-to-compare-column-values-rows-in-same-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Table details are in the hyper link, Pls let me know if anyone has come across such scenario to pull the records.
– Radha Jogendra
Nov 21 '18 at 20:30
1
What have you tried so far?
– lurker
Nov 21 '18 at 20:56
No link, please. Add sample table data and the expected result as properly formatted text. Also show us your current query attempt. BTW, are you using MySQL or DB2?
– jarlh
Nov 21 '18 at 20:58
What if firstname values are same but lastname values are different ? Will that still be considered a different row ?
– Madhur Bhaiya
Nov 21 '18 at 21:03
1
Mysql and db2 are two different products with different syntax and features. Therefore I removed the conflicting product tags. Pls add the one back that you really use.
– Shadow
Nov 21 '18 at 21:23