DISTINCT column combination with permutations
4
My postgresql column structure looks like this:
id | from | to
---------------
1 | A | B
2 | A | B
3 | C | D
Now I want an result which looks like this:
res
-----
'A:B'
'B:A'
'C:D'
'D:C'
where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.
postgresql distinct
asked 2 days ago
nalinali
1335
add a comment |
4
My postgresql column structure looks like this:
id | from | to
---------------
1 | A | B
2 | A | B
3 | C | D
Now I want an result which looks like this:
res
-----
'A:B'
'B:A'
'C:D'
'D:C'
where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.
postgresql distinct
asked 2 days ago
nalinali
1335
add a comment |
4
4
4
My postgresql column structure looks like this:
id | from | to
---------------
1 | A | B
2 | A | B
3 | C | D
Now I want an result which looks like this:
res
-----
'A:B'
'B:A'
'C:D'
'D:C'
where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.
postgresql distinct
asked 2 days ago
nalinali
1335
My postgresql column structure looks like this:
id | from | to
---------------
1 | A | B
2 | A | B
3 | C | D
Now I want an result which looks like this:
res
-----
'A:B'
'B:A'
'C:D'
'D:C'
where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.
postgresql distinct
postgresql distinct
asked 2 days ago
nalinali
1335
asked 2 days ago
nalinali
1335
asked 2 days ago
nalinali
1335
asked 2 days ago
nalinali
1335
asked 2 days ago
nalinali
1335
1335
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
5
This looks more like a stack overflow question yet Union will give a distinct combination.
SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Wrap fieldnames with doublequotes (FromandToare reserved words). And field alias in second subquery is excess.
– Akina
2 days ago
1
what is default in postgre @Akina is it a double quote or [ ]
– Ajan Balakumaran
2 days ago
2
the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Identifiers and Key Words. Square brackets are not mentioned at all.
– Akina
2 days ago
1
Noted with thanks
– Ajan Balakumaran
2 days ago
2
One more interesting - quoted identifiers are case-sensitive ("From" and "from" difffers) whereas non-quoted identifiers are case-insensitive.
– Akina
2 days ago
|
show 1 more comment
3
You can also use least() and greatest() together with distinct. That would preserve the individual values without the need to concatenate them.
select distinct least("from", "to"), greatest("from", "to")
from the_table;
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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');
});
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%2fdba.stackexchange.com%2fquestions%2f233642%2fdistinct-column-combination-with-permutations%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
5
This looks more like a stack overflow question yet Union will give a distinct combination.
SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Wrap fieldnames with doublequotes (FromandToare reserved words). And field alias in second subquery is excess.
– Akina
2 days ago
1
what is default in postgre @Akina is it a double quote or [ ]
– Ajan Balakumaran
2 days ago
2
the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Identifiers and Key Words. Square brackets are not mentioned at all.
– Akina
2 days ago
1
Noted with thanks
– Ajan Balakumaran
2 days ago
2
One more interesting - quoted identifiers are case-sensitive ("From" and "from" difffers) whereas non-quoted identifiers are case-insensitive.
– Akina
2 days ago
|
show 1 more comment
5
This looks more like a stack overflow question yet Union will give a distinct combination.
SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Wrap fieldnames with doublequotes (FromandToare reserved words). And field alias in second subquery is excess.
– Akina
2 days ago
1
what is default in postgre @Akina is it a double quote or [ ]
– Ajan Balakumaran
2 days ago
2
the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Identifiers and Key Words. Square brackets are not mentioned at all.
– Akina
2 days ago
1
Noted with thanks
– Ajan Balakumaran
2 days ago
2
One more interesting - quoted identifiers are case-sensitive ("From" and "from" difffers) whereas non-quoted identifiers are case-insensitive.
– Akina
2 days ago
|
show 1 more comment
5
5
5
This looks more like a stack overflow question yet Union will give a distinct combination.
SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
This looks more like a stack overflow question yet Union will give a distinct combination.
SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 days ago
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
1664
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Ajan Balakumaran is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
Wrap fieldnames with doublequotes (FromandToare reserved words). And field alias in second subquery is excess.
– Akina
2 days ago
1
what is default in postgre @Akina is it a double quote or [ ]
– Ajan Balakumaran
2 days ago
2
the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Identifiers and Key Words. Square brackets are not mentioned at all.
– Akina
2 days ago
1
Noted with thanks
– Ajan Balakumaran
2 days ago
2
One more interesting - quoted identifiers are case-sensitive ("From" and "from" difffers) whereas non-quoted identifiers are case-insensitive.
– Akina
2 days ago
|
show 1 more comment
3
Wrap fieldnames with doublequotes (FromandToare reserved words). And field alias in second subquery is excess.
– Akina
2 days ago
1
what is default in postgre @Akina is it a double quote or [ ]
– Ajan Balakumaran
2 days ago
2
the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Identifiers and Key Words. Square brackets are not mentioned at all.
– Akina
2 days ago
1
Noted with thanks
– Ajan Balakumaran
2 days ago
2
One more interesting - quoted identifiers are case-sensitive ("From" and "from" difffers) whereas non-quoted identifiers are case-insensitive.
– Akina
2 days ago
3
3
Wrap fieldnames with doublequotes (
From and To are reserved words). And field alias in second subquery is excess.– Akina
2 days ago
Wrap fieldnames with doublequotes (
From and To are reserved words). And field alias in second subquery is excess.– Akina
2 days ago
1
1
what is default in postgre @Akina is it a double quote or [ ]
– Ajan Balakumaran
2 days ago
what is default in postgre @Akina is it a double quote or [ ]
– Ajan Balakumaran
2 days ago
2
2
the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Identifiers and Key Words. Square brackets are not mentioned at all.
– Akina
2 days ago
the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Identifiers and Key Words. Square brackets are not mentioned at all.
– Akina
2 days ago
1
1
Noted with thanks
– Ajan Balakumaran
2 days ago
Noted with thanks
– Ajan Balakumaran
2 days ago
2
2
One more interesting - quoted identifiers are case-sensitive ("From" and "from" difffers) whereas non-quoted identifiers are case-insensitive.
– Akina
2 days ago
One more interesting - quoted identifiers are case-sensitive ("From" and "from" difffers) whereas non-quoted identifiers are case-insensitive.
– Akina
2 days ago
|
show 1 more comment
3
You can also use least() and greatest() together with distinct. That would preserve the individual values without the need to concatenate them.
select distinct least("from", "to"), greatest("from", "to")
from the_table;
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
add a comment |
3
You can also use least() and greatest() together with distinct. That would preserve the individual values without the need to concatenate them.
select distinct least("from", "to"), greatest("from", "to")
from the_table;
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
add a comment |
3
3
3
You can also use least() and greatest() together with distinct. That would preserve the individual values without the need to concatenate them.
select distinct least("from", "to"), greatest("from", "to")
from the_table;
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
You can also use least() and greatest() together with distinct. That would preserve the individual values without the need to concatenate them.
select distinct least("from", "to"), greatest("from", "to")
from the_table;
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
answered 2 days ago
a_horse_with_no_namea_horse_with_no_name
41.4k778115
41.4k778115
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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');
});
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%2fdba.stackexchange.com%2fquestions%2f233642%2fdistinct-column-combination-with-permutations%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
