DISTINCT column combination with permutations
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
add a comment |
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
add a comment |
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
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
1335
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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
New contributor
3
Wrap fieldnames with doublequotes (From
andTo
are 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
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;
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
});
}
});
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
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
New contributor
3
Wrap fieldnames with doublequotes (From
andTo
are 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
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
New contributor
3
Wrap fieldnames with doublequotes (From
andTo
are 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
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
New contributor
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
New contributor
edited 2 days ago
New contributor
answered 2 days ago
Ajan BalakumaranAjan Balakumaran
1664
1664
New contributor
New contributor
3
Wrap fieldnames with doublequotes (From
andTo
are 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 (From
andTo
are 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
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;
add a comment |
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;
add a comment |
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;
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
41.4k778115
add a comment |
add a comment |
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.
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