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.










share|improve this question



























    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.










    share|improve this question

























      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.










      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 2 days ago









      nalinali

      1335




      1335






















          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





          share|improve this answer










          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 (From and To 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














          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;





          share|improve this answer
























            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


















            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





            share|improve this answer










            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 (From and To 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
















            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





            share|improve this answer










            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 (From and To 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














            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





            share|improve this answer










            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






            share|improve this answer










            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.









            share|improve this answer



            share|improve this answer








            edited 2 days ago





















            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




            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 (From and To 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





              Wrap fieldnames with doublequotes (From and To 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













            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;





            share|improve this answer




























              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;





              share|improve this answer


























                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;





                share|improve this answer













                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;






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 2 days ago









                a_horse_with_no_namea_horse_with_no_name

                41.4k778115




                41.4k778115






























                    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














                    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





















































                    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







                    Popular posts from this blog

                    數位音樂下載

                    格利澤436b

                    When can things happen in Etherscan, such as the picture below?