SQL Group By using Two Keys The Next CEO of Stack OverflowHow can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeInserting multiple rows in a single SQL query?Retrieving the last record in each group - MySQLHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableSelect first row in each GROUP BY group?Select Distinct 2 ColumnsHow to import an SQL file using the command line in MySQL?
Are there any limitations on attacking while grappling?
How do we know the LHC results are robust?
What connection does MS Office have to Netscape Navigator?
Why do airplanes bank sharply to the right after air-to-air refueling?
MessageLevel in QGIS3
Do I need to enable Dev Hub in my PROD Org?
Interfacing a button to MCU (and PC) with 50m long cable
What was the first Unix version to run on a microcomputer?
How to start emacs in "nothing" mode (`fundamental-mode`)
Would a completely good Muggle be able to use a wand?
Inappropriate reference requests from Journal reviewers
Can you replace a racial trait cantrip when leveling up?
If the heap is initialized for security, then why is the stack uninitialized?
How to avoid supervisors with prejudiced views?
What is the result of assigning to std::vector<T>::begin()?
Received an invoice from my ex-employer billing me for training; how to handle?
WOW air has ceased operation, can I get my tickets refunded?
Does it take more energy to get to Venus or to Mars?
I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin
In excess I'm lethal
Why do professional authors make "consistency" mistakes? And how to avoid them?
What is "(CFMCC)" on an ILS approach chart?
What does "Its cash flow is deeply negative" mean?
Why do we use the plural of movies in this phrase "We went to the movies last night."?
SQL Group By using Two Keys
The Next CEO of Stack OverflowHow can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeInserting multiple rows in a single SQL query?Retrieving the last record in each group - MySQLHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableSelect first row in each GROUP BY group?Select Distinct 2 ColumnsHow to import an SQL file using the command line in MySQL?
I'm looking to write a query to group by ID1, ID2, but only return the IDs where there is >1 unique ID1 for ID2.
I have data like this:
+------+------+
| ID1 | ID2 |
+------+------+
|1 |A |
+------+------+
|1 |A |
+------+------+
|2 |A |
+------+------+
|3 |B |
+------+------+
|3 |B |
+------+------+
|4 |C |
+------+------+
|5 |C |
+------+------+
|6 |D |
+------+------+
|6 |D |
+------+------+
|7 |E |
+------+------+
Ideally, my output will look like this:
+------+
| ID2 |
+------+
|A |
+------+
|C |
+------+
Notice how there are >1 record for ID2 = 'B' or ID2 = 'D', but they have the same ID1. In example A, even though there are duplicate "1" values of ID1 I would still like to select it because there is another Unique ID1 - "2".
sql hadoop impala
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
add a comment |
I'm looking to write a query to group by ID1, ID2, but only return the IDs where there is >1 unique ID1 for ID2.
I have data like this:
+------+------+
| ID1 | ID2 |
+------+------+
|1 |A |
+------+------+
|1 |A |
+------+------+
|2 |A |
+------+------+
|3 |B |
+------+------+
|3 |B |
+------+------+
|4 |C |
+------+------+
|5 |C |
+------+------+
|6 |D |
+------+------+
|6 |D |
+------+------+
|7 |E |
+------+------+
Ideally, my output will look like this:
+------+
| ID2 |
+------+
|A |
+------+
|C |
+------+
Notice how there are >1 record for ID2 = 'B' or ID2 = 'D', but they have the same ID1. In example A, even though there are duplicate "1" values of ID1 I would still like to select it because there is another Unique ID1 - "2".
sql hadoop impala
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
add a comment |
I'm looking to write a query to group by ID1, ID2, but only return the IDs where there is >1 unique ID1 for ID2.
I have data like this:
+------+------+
| ID1 | ID2 |
+------+------+
|1 |A |
+------+------+
|1 |A |
+------+------+
|2 |A |
+------+------+
|3 |B |
+------+------+
|3 |B |
+------+------+
|4 |C |
+------+------+
|5 |C |
+------+------+
|6 |D |
+------+------+
|6 |D |
+------+------+
|7 |E |
+------+------+
Ideally, my output will look like this:
+------+
| ID2 |
+------+
|A |
+------+
|C |
+------+
Notice how there are >1 record for ID2 = 'B' or ID2 = 'D', but they have the same ID1. In example A, even though there are duplicate "1" values of ID1 I would still like to select it because there is another Unique ID1 - "2".
sql hadoop impala
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
I'm looking to write a query to group by ID1, ID2, but only return the IDs where there is >1 unique ID1 for ID2.
I have data like this:
+------+------+
| ID1 | ID2 |
+------+------+
|1 |A |
+------+------+
|1 |A |
+------+------+
|2 |A |
+------+------+
|3 |B |
+------+------+
|3 |B |
+------+------+
|4 |C |
+------+------+
|5 |C |
+------+------+
|6 |D |
+------+------+
|6 |D |
+------+------+
|7 |E |
+------+------+
Ideally, my output will look like this:
+------+
| ID2 |
+------+
|A |
+------+
|C |
+------+
Notice how there are >1 record for ID2 = 'B' or ID2 = 'D', but they have the same ID1. In example A, even though there are duplicate "1" values of ID1 I would still like to select it because there is another Unique ID1 - "2".
sql hadoop impala
sql hadoop impala
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
asked Mar 8 at 14:22
DukeLukeDukeLuke
206113
206113
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
You can use COUNT(DISTINCT ID1) in the having clause as the following:
SELECT ID2
FROM tbl
GROUP BY sID2
HAVING COUNT(DISTINCT ID1) > 1
answered Mar 8 at 14:47
radrad
1,3961814
add a comment |
select ID2
from t
group by ID2
having count(distinct ID1) > 1
Not sure if Impala has count(distinct), but this is fairly standard so I am going to assume it. The having clause is applied after the group by, so it only keeps the data you are looking for.
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
add a comment |
I would recommend:
select ID2
from t
group by ID2
having min(ID1) <> max(ID1);
I think that min() and max() have much better performance characteristics than count(distinct).
In fact, I would expect this to work better than count(distinct):
select id2
from (select distinct id1, id2
from t
) x
group by id2
having count(*) > 1;
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
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%2f55065142%2fsql-group-by-using-two-keys%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
You can use COUNT(DISTINCT ID1) in the having clause as the following:
SELECT ID2
FROM tbl
GROUP BY sID2
HAVING COUNT(DISTINCT ID1) > 1
answered Mar 8 at 14:47
radrad
1,3961814
add a comment |
You can use COUNT(DISTINCT ID1) in the having clause as the following:
SELECT ID2
FROM tbl
GROUP BY sID2
HAVING COUNT(DISTINCT ID1) > 1
answered Mar 8 at 14:47
radrad
1,3961814
add a comment |
You can use COUNT(DISTINCT ID1) in the having clause as the following:
SELECT ID2
FROM tbl
GROUP BY sID2
HAVING COUNT(DISTINCT ID1) > 1
answered Mar 8 at 14:47
radrad
1,3961814
You can use COUNT(DISTINCT ID1) in the having clause as the following:
SELECT ID2
FROM tbl
GROUP BY sID2
HAVING COUNT(DISTINCT ID1) > 1
answered Mar 8 at 14:47
radrad
1,3961814
answered Mar 8 at 14:47
radrad
1,3961814
answered Mar 8 at 14:47
radrad
1,3961814
answered Mar 8 at 14:47
radrad
1,3961814
1,3961814
add a comment |
add a comment |
select ID2
from t
group by ID2
having count(distinct ID1) > 1
Not sure if Impala has count(distinct), but this is fairly standard so I am going to assume it. The having clause is applied after the group by, so it only keeps the data you are looking for.
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
add a comment |
select ID2
from t
group by ID2
having count(distinct ID1) > 1
Not sure if Impala has count(distinct), but this is fairly standard so I am going to assume it. The having clause is applied after the group by, so it only keeps the data you are looking for.
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
add a comment |
select ID2
from t
group by ID2
having count(distinct ID1) > 1
Not sure if Impala has count(distinct), but this is fairly standard so I am going to assume it. The having clause is applied after the group by, so it only keeps the data you are looking for.
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
select ID2
from t
group by ID2
having count(distinct ID1) > 1
Not sure if Impala has count(distinct), but this is fairly standard so I am going to assume it. The having clause is applied after the group by, so it only keeps the data you are looking for.
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
answered Mar 8 at 14:48
GuillaumeGuillaume
1,53011227
1,53011227
add a comment |
add a comment |
I would recommend:
select ID2
from t
group by ID2
having min(ID1) <> max(ID1);
I think that min() and max() have much better performance characteristics than count(distinct).
In fact, I would expect this to work better than count(distinct):
select id2
from (select distinct id1, id2
from t
) x
group by id2
having count(*) > 1;
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
add a comment |
I would recommend:
select ID2
from t
group by ID2
having min(ID1) <> max(ID1);
I think that min() and max() have much better performance characteristics than count(distinct).
In fact, I would expect this to work better than count(distinct):
select id2
from (select distinct id1, id2
from t
) x
group by id2
having count(*) > 1;
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
add a comment |
I would recommend:
select ID2
from t
group by ID2
having min(ID1) <> max(ID1);
I think that min() and max() have much better performance characteristics than count(distinct).
In fact, I would expect this to work better than count(distinct):
select id2
from (select distinct id1, id2
from t
) x
group by id2
having count(*) > 1;
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
I would recommend:
select ID2
from t
group by ID2
having min(ID1) <> max(ID1);
I think that min() and max() have much better performance characteristics than count(distinct).
In fact, I would expect this to work better than count(distinct):
select id2
from (select distinct id1, id2
from t
) x
group by id2
having count(*) > 1;
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
answered Mar 8 at 15:08
Gordon LinoffGordon Linoff
792k36316419
792k36316419
add a comment |
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%2f55065142%2fsql-group-by-using-two-keys%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
