SQL show ONLY displayed duplicates after filtering out duplicates with same results in other column

SELECT DISTINCT(code), connection, standing 

FROM data

WHERE code IN (SELECT code 

               FROM data 

               GROUP BY code 

               HAVING COUNT(*)>1)

ORDER BY code

The problem I have is that it shows all duplicates.

Basically I wanted to show duplicates if they have different values in either connection or standing but It also shows duplicates (in only 1 row because of the DISTINCT) that have same value in either connection or stand.

What I have and want:

Code   Connection  Standing

----------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

But I also have:

Code   Connection  Standing

---------------------------

WWW    YYY         YYY

And that code is there only once because of the distinct.

So can I filter out single results after the distinct?

Not sure if its understandable but I hope so.

EDIT: To add more sample of what my problem is:

Code   Connection  Standing

---------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

YYY    SSS         YYY

YYY    SSS         ZZZ

TTT    SSS         YYY

I want to get rid of TTT line because its not really the duplicate I am looking for. I want to see duplicates that have different values in either connection or standing.

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

asked Nov 22 '18 at 10:21

Sukonbu

1

DISTINCT is NOT a function. distinct (a),b,c is the same as distinct a,(b),c or distinct a,b,c. It always applies to all columns in the select list.

– a_horse_with_no_name
Nov 22 '18 at 10:24

DISTINCT is not a function, it's a part of SELECT DISTINCT and applies on the whole selected rows. To avoid confusion, remove those redundant parentheses and simply write SELECT DISTINCT code , connection, standing FROM data....

– jarlh
Nov 22 '18 at 10:24

1

Add a few more rows of sample data, and also adjust the expected result accordingly.

– jarlh
Nov 22 '18 at 10:25

I need to see duplicates in column Code that have different values in either Connection or Standing and I need to filter out if in Code there is single result (that is duplicate but have same results in Connection or Standing so it hides because of DISTINCT) and these single results after filtering I need to hide.

– Sukonbu
Nov 22 '18 at 10:45

Is is oracle or sqlserver?

– Shaili
Nov 22 '18 at 11:13

|
show 4 more comments

SELECT DISTINCT(code), connection, standing 

FROM data

WHERE code IN (SELECT code 

               FROM data 

               GROUP BY code 

               HAVING COUNT(*)>1)

ORDER BY code

The problem I have is that it shows all duplicates.

What I have and want:

Code   Connection  Standing

----------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

But I also have:

Code   Connection  Standing

---------------------------

WWW    YYY         YYY

And that code is there only once because of the distinct.

So can I filter out single results after the distinct?

Not sure if its understandable but I hope so.

EDIT: To add more sample of what my problem is:

Code   Connection  Standing

---------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

YYY    SSS         YYY

YYY    SSS         ZZZ

TTT    SSS         YYY

I want to get rid of TTT line because its not really the duplicate I am looking for. I want to see duplicates that have different values in either connection or standing.

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

asked Nov 22 '18 at 10:21

Sukonbu

1

DISTINCT is NOT a function. distinct (a),b,c is the same as distinct a,(b),c or distinct a,b,c. It always applies to all columns in the select list.

– a_horse_with_no_name
Nov 22 '18 at 10:24

DISTINCT is not a function, it's a part of SELECT DISTINCT and applies on the whole selected rows. To avoid confusion, remove those redundant parentheses and simply write SELECT DISTINCT code , connection, standing FROM data....

– jarlh
Nov 22 '18 at 10:24

1

Add a few more rows of sample data, and also adjust the expected result accordingly.

– jarlh
Nov 22 '18 at 10:25

I need to see duplicates in column Code that have different values in either Connection or Standing and I need to filter out if in Code there is single result (that is duplicate but have same results in Connection or Standing so it hides because of DISTINCT) and these single results after filtering I need to hide.

– Sukonbu
Nov 22 '18 at 10:45

Is is oracle or sqlserver?

– Shaili
Nov 22 '18 at 11:13

|
show 4 more comments

SELECT DISTINCT(code), connection, standing 

FROM data

WHERE code IN (SELECT code 

               FROM data 

               GROUP BY code 

               HAVING COUNT(*)>1)

ORDER BY code

The problem I have is that it shows all duplicates.

What I have and want:

Code   Connection  Standing

----------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

But I also have:

Code   Connection  Standing

---------------------------

WWW    YYY         YYY

And that code is there only once because of the distinct.

So can I filter out single results after the distinct?

Not sure if its understandable but I hope so.

EDIT: To add more sample of what my problem is:

Code   Connection  Standing

---------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

YYY    SSS         YYY

YYY    SSS         ZZZ

TTT    SSS         YYY

I want to get rid of TTT line because its not really the duplicate I am looking for. I want to see duplicates that have different values in either connection or standing.

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

asked Nov 22 '18 at 10:21

Sukonbu

SELECT DISTINCT(code), connection, standing 

FROM data

WHERE code IN (SELECT code 

               FROM data 

               GROUP BY code 

               HAVING COUNT(*)>1)

ORDER BY code

The problem I have is that it shows all duplicates.

What I have and want:

Code   Connection  Standing

----------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

But I also have:

Code   Connection  Standing

---------------------------

WWW    YYY         YYY

And that code is there only once because of the distinct.

So can I filter out single results after the distinct?

Not sure if its understandable but I hope so.

EDIT: To add more sample of what my problem is:

Code   Connection  Standing

---------------------------

XXX    YYY         YYY

XXX    ZZZ         YYY

YYY    SSS         YYY

YYY    SSS         ZZZ

TTT    SSS         YYY

I want to get rid of TTT line because its not really the duplicate I am looking for. I want to see duplicates that have different values in either connection or standing.

sql sql-server tsql

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

asked Nov 22 '18 at 10:21

Sukonbu

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

asked Nov 22 '18 at 10:21

Sukonbu

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

edited Nov 23 '18 at 6:08

Rahul Neekhra

6001627

asked Nov 22 '18 at 10:21

Sukonbu

asked Nov 22 '18 at 10:21

Sukonbu

asked Nov 22 '18 at 10:21

Sukonbu

1

DISTINCT is NOT a function. distinct (a),b,c is the same as distinct a,(b),c or distinct a,b,c. It always applies to all columns in the select list.

– a_horse_with_no_name
Nov 22 '18 at 10:24

DISTINCT is not a function, it's a part of SELECT DISTINCT and applies on the whole selected rows. To avoid confusion, remove those redundant parentheses and simply write SELECT DISTINCT code , connection, standing FROM data....

– jarlh
Nov 22 '18 at 10:24

1

Add a few more rows of sample data, and also adjust the expected result accordingly.

– jarlh
Nov 22 '18 at 10:25

I need to see duplicates in column Code that have different values in either Connection or Standing and I need to filter out if in Code there is single result (that is duplicate but have same results in Connection or Standing so it hides because of DISTINCT) and these single results after filtering I need to hide.

– Sukonbu
Nov 22 '18 at 10:45

Is is oracle or sqlserver?

– Shaili
Nov 22 '18 at 11:13

|
show 4 more comments

1

DISTINCT is NOT a function. distinct (a),b,c is the same as distinct a,(b),c or distinct a,b,c. It always applies to all columns in the select list.

– a_horse_with_no_name
Nov 22 '18 at 10:24

DISTINCT is not a function, it's a part of SELECT DISTINCT and applies on the whole selected rows. To avoid confusion, remove those redundant parentheses and simply write SELECT DISTINCT code , connection, standing FROM data....

– jarlh
Nov 22 '18 at 10:24

1

Add a few more rows of sample data, and also adjust the expected result accordingly.

– jarlh
Nov 22 '18 at 10:25

I need to see duplicates in column Code that have different values in either Connection or Standing and I need to filter out if in Code there is single result (that is duplicate but have same results in Connection or Standing so it hides because of DISTINCT) and these single results after filtering I need to hide.

– Sukonbu
Nov 22 '18 at 10:45

Is is oracle or sqlserver?

– Shaili
Nov 22 '18 at 11:13

DISTINCT is NOT a function. distinct (a),b,c is the same as distinct a,(b),c or distinct a,b,c. It always applies to all columns in the select list.

– a_horse_with_no_name
Nov 22 '18 at 10:24

DISTINCT is not a function, it's a part of SELECT DISTINCT and applies on the whole selected rows. To avoid confusion, remove those redundant parentheses and simply write SELECT DISTINCT code , connection, standing FROM data....

– jarlh
Nov 22 '18 at 10:24

Add a few more rows of sample data, and also adjust the expected result accordingly.

– jarlh
Nov 22 '18 at 10:25

I need to see duplicates in column Code that have different values in either Connection or Standing and I need to filter out if in Code there is single result (that is duplicate but have same results in Connection or Standing so it hides because of DISTINCT) and these single results after filtering I need to hide.

– Sukonbu
Nov 22 '18 at 10:45

Is is oracle or sqlserver?

– Shaili
Nov 22 '18 at 11:13

|
show 4 more comments

1 Answer
1

active

oldest

votes

I believe there are three major steps involved in what you are looking for:

Get the distincts combinations of Code, Connection and Standing

Get all the values of Code which exist more than once in the resul from 1.

Get all the rows from the result from 1. whose Code exist in the result from 2.

One way from achieving this is with the help of Common Table Expressions and Subqueries:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T)



SELECT CTE.Code,Connection, Standing FROM CTE INNER JOIN 

(SELECT Code FROM CTE GROUP BY Code HAVING COUNT(*) >1) AS tCode ON tCode.Code = CTE.Code

Another way may be using CTEs and Window Functions:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount 

FROM CTE) 

SELECT Code, Connection, Standing FROM CTE2 WHERE CodeCount > 1

This last option may prove more powerful depending on what you want to do. For example, if you would like to know how many times a Connection value or Standing value exist in relation to a certain Code value:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount, 

COUNT(*) OVER (PARTITION BY Code, Connection)

as ConnectionByCodeCount, COUNT(*) OVER (PARTITION BY Code, Standing)

as StandingByCodeCount 

FROM CTE) 

SELECT Code, Connection, ConnectionByCodeCount, Standing, StandingByCodeCount FROM CTE2 WHERE CodeCount > 1

Would return:

Code| Connection| ConnectionByCodeCount  | Standing| StandingByCodeCount

----| ----------| -----------------------| --------| -------------------

XXX | YYY       | 1                      | YYY     | 2 (times YYY, Code XXX)

XXX | ZZZ       | 1                      | YYY     | 2

YYY | SSS       | 2 (times SSS, Code YYY)| YYY     | 1

YYY | SSS       | 2                      | ZZZ     | 1

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53428743%2fsql-show-only-displayed-duplicates-after-filtering-out-duplicates-with-same-resu%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

I believe there are three major steps involved in what you are looking for:

Get the distincts combinations of Code, Connection and Standing

Get all the values of Code which exist more than once in the resul from 1.

Get all the rows from the result from 1. whose Code exist in the result from 2.

One way from achieving this is with the help of Common Table Expressions and Subqueries:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T)



SELECT CTE.Code,Connection, Standing FROM CTE INNER JOIN 

(SELECT Code FROM CTE GROUP BY Code HAVING COUNT(*) >1) AS tCode ON tCode.Code = CTE.Code

Another way may be using CTEs and Window Functions:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount 

FROM CTE) 

SELECT Code, Connection, Standing FROM CTE2 WHERE CodeCount > 1

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount, 

COUNT(*) OVER (PARTITION BY Code, Connection)

as ConnectionByCodeCount, COUNT(*) OVER (PARTITION BY Code, Standing)

as StandingByCodeCount 

FROM CTE) 

SELECT Code, Connection, ConnectionByCodeCount, Standing, StandingByCodeCount FROM CTE2 WHERE CodeCount > 1

Would return:

Code| Connection| ConnectionByCodeCount  | Standing| StandingByCodeCount

----| ----------| -----------------------| --------| -------------------

XXX | YYY       | 1                      | YYY     | 2 (times YYY, Code XXX)

XXX | ZZZ       | 1                      | YYY     | 2

YYY | SSS       | 2 (times SSS, Code YYY)| YYY     | 1

YYY | SSS       | 2                      | ZZZ     | 1

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

add a comment |

I believe there are three major steps involved in what you are looking for:

Get the distincts combinations of Code, Connection and Standing

Get all the values of Code which exist more than once in the resul from 1.

Get all the rows from the result from 1. whose Code exist in the result from 2.

One way from achieving this is with the help of Common Table Expressions and Subqueries:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T)



SELECT CTE.Code,Connection, Standing FROM CTE INNER JOIN 

(SELECT Code FROM CTE GROUP BY Code HAVING COUNT(*) >1) AS tCode ON tCode.Code = CTE.Code

Another way may be using CTEs and Window Functions:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount 

FROM CTE) 

SELECT Code, Connection, Standing FROM CTE2 WHERE CodeCount > 1

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount, 

COUNT(*) OVER (PARTITION BY Code, Connection)

as ConnectionByCodeCount, COUNT(*) OVER (PARTITION BY Code, Standing)

as StandingByCodeCount 

FROM CTE) 

SELECT Code, Connection, ConnectionByCodeCount, Standing, StandingByCodeCount FROM CTE2 WHERE CodeCount > 1

Would return:

Code| Connection| ConnectionByCodeCount  | Standing| StandingByCodeCount

----| ----------| -----------------------| --------| -------------------

XXX | YYY       | 1                      | YYY     | 2 (times YYY, Code XXX)

XXX | ZZZ       | 1                      | YYY     | 2

YYY | SSS       | 2 (times SSS, Code YYY)| YYY     | 1

YYY | SSS       | 2                      | ZZZ     | 1

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

add a comment |

I believe there are three major steps involved in what you are looking for:

Get the distincts combinations of Code, Connection and Standing

Get all the values of Code which exist more than once in the resul from 1.

Get all the rows from the result from 1. whose Code exist in the result from 2.

One way from achieving this is with the help of Common Table Expressions and Subqueries:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T)



SELECT CTE.Code,Connection, Standing FROM CTE INNER JOIN 

(SELECT Code FROM CTE GROUP BY Code HAVING COUNT(*) >1) AS tCode ON tCode.Code = CTE.Code

Another way may be using CTEs and Window Functions:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount 

FROM CTE) 

SELECT Code, Connection, Standing FROM CTE2 WHERE CodeCount > 1

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount, 

COUNT(*) OVER (PARTITION BY Code, Connection)

as ConnectionByCodeCount, COUNT(*) OVER (PARTITION BY Code, Standing)

as StandingByCodeCount 

FROM CTE) 

SELECT Code, Connection, ConnectionByCodeCount, Standing, StandingByCodeCount FROM CTE2 WHERE CodeCount > 1

Would return:

Code| Connection| ConnectionByCodeCount  | Standing| StandingByCodeCount

----| ----------| -----------------------| --------| -------------------

XXX | YYY       | 1                      | YYY     | 2 (times YYY, Code XXX)

XXX | ZZZ       | 1                      | YYY     | 2

YYY | SSS       | 2 (times SSS, Code YYY)| YYY     | 1

YYY | SSS       | 2                      | ZZZ     | 1

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

I believe there are three major steps involved in what you are looking for:

Get the distincts combinations of Code, Connection and Standing

Get all the values of Code which exist more than once in the resul from 1.

Get all the rows from the result from 1. whose Code exist in the result from 2.

One way from achieving this is with the help of Common Table Expressions and Subqueries:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T)



SELECT CTE.Code,Connection, Standing FROM CTE INNER JOIN 

(SELECT Code FROM CTE GROUP BY Code HAVING COUNT(*) >1) AS tCode ON tCode.Code = CTE.Code

Another way may be using CTEs and Window Functions:

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount 

FROM CTE) 

SELECT Code, Connection, Standing FROM CTE2 WHERE CodeCount > 1

WITH CTE AS (

SELECT DISTINCT Code, Connection,Standing FROM @T),

CTE2 as (

SELECT CTE.Code,Connection, Standing, COUNT(*) OVER (PARTITION BY Code) as CodeCount, 

COUNT(*) OVER (PARTITION BY Code, Connection)

as ConnectionByCodeCount, COUNT(*) OVER (PARTITION BY Code, Standing)

as StandingByCodeCount 

FROM CTE) 

SELECT Code, Connection, ConnectionByCodeCount, Standing, StandingByCodeCount FROM CTE2 WHERE CodeCount > 1

Would return:

Code| Connection| ConnectionByCodeCount  | Standing| StandingByCodeCount

----| ----------| -----------------------| --------| -------------------

XXX | YYY       | 1                      | YYY     | 2 (times YYY, Code XXX)

XXX | ZZZ       | 1                      | YYY     | 2

YYY | SSS       | 2 (times SSS, Code YYY)| YYY     | 1

YYY | SSS       | 2                      | ZZZ     | 1

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

answered Nov 22 '18 at 16:36

Emilio Lucas Ceroleni

1,2252610

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu