CODEWITHSUNDEEP
mysqlsqlselect
cucuru
cucuru

Reputation: 25

distinct values of different columns

I'm not sure it's possible.

I have a table with 3 columns, and I want to get different values of each one, there is an example:

A      B        C
--------------------
a1     b1       c1
a1     b2       c2
a1     b3       c1

The expected result is:

A      B       C
-----------------
a1     b1      c1
       b2      c2
       b3

So, it will be the union of:

SELECT DISTINCT A FROM myTable
SELECT DISTINCT B FROM myTable
SELECT DISTINCT C FROM myTable

Using UNION I get the result in one column.

Is this posible to do?

Upvotes: 0

Views: 91

Answers (1)

toonice
toonice

Reputation: 2236

Please try the following...

SELECT COALESCE( a, '' ) AS a,
       COALESCE( b, '' ) AS b,
       COALESCE( c, '' ) AS c
FROM ( SELECT a,
              ROW_NUMBER() OVER ( ORDER BY a ) AS recordNumber
       FROM ( SELECT a
              FROM myTable
              GROUP BY a
            ) aFinder
     ) aNumberedFinder
FULL JOIN ( SELECT b,
                   ROW_NUMBER() OVER ( ORDER BY b ) AS recordNumber
            FROM ( SELECT b
                   FROM myTable
                   GROUP BY b
                 ) bFinder
          ) bNumberedFinder ON bNumberedFinder.recordNumber = aNumberedFinder.recordNumber
FULL JOIN ( SELECT c,
                   ROW_NUMBER() OVER ( ORDER BY c ) AS recordNumber
            FROM ( SELECT c
                   FROM myTable
                   GROUP BY c
                 ) cFinder
          ) cNumberedFinder ON cNumberedFinder.recordNumber = aNumberedFinder.recordNumber
                            OR cNumberedFinder.recordNumber = bNumberedFinder.recordNumber;

This statement starts with the following subquery...

SELECT a
FROM myTable
GROUP BY a

This subquery produces a list of the unique values in field a. The following subquery reproduces this list with record (aka row) numbers...

SELECT a,
       ROW_NUMBER() OVER ( ORDER BY a ) AS recordNumber
FROM ( SELECT a
       FROM myTable
       GROUP BY a
     ) aFinder

Similar subqueries are used to produce numbered lists of the unique values in fields b and c.

A FULL OUTER JOIN (abbreviated here to FULL JOIN) is then performed between the lists for a and b, with their record numbers being used as the common / joining value.

Note : An INNER JOIN would only have returned as many records as there are in the shorter list of the two. A LEFT JOIN would only have been effective if the list on the left of the JOIN were of greater or longer length than the list on the right. Similar logic applies to the usage of a RIGHT JOIN. A FULL JOIN will join the two lists in the manner of a LEFT JOIN or RIGHT JOIN irrespective of which list is longer.

A FULL JOIN is then performed between the above joined dataset and the list for c where it finds a common value in either list.

The values of a, b and c are then selected from the finally joined dataset, with the COALESCE() function replacing the occurrence of any NULL values, such as those generated by the joining process, with the empty string ('')

If you have any questions or comments, then please feel free to post a Comment accordingly.

Appendix

My statement was tested against a database created using the following script...

CREATE TABLE myTable
(
    a   VARCHAR( 5 ),
    b   VARCHAR( 5 ),
    c   VARCHAR( 5 )
);
INSERT INTO myTable ( a,
                      b,
                      c
                    )
VALUES ( 'a1', 'b1', 'c1' ),
       ( 'a1', 'b2', 'c2' ),
       ( 'a1', 'b3', 'c1' );

Further Reading

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql (on using the COALESCE() function in Transact-SQL)

https://www.w3schools.com/sql/sql_join.asp (on the various types of horizontal JOIN - the Venn diagram is useful)

https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx (on using OUTER JOIN in SQL-Server)

https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql (on using ROW_NUMBER() in Transact-SQL)

Upvotes: 2

Related Questions