CODEWITHSUNDEEP
phpmysqlselectdistinct
Pradyumna Challa
Pradyumna Challa

Reputation: 1145

Select unique from all columns into a single column

I have 4 columns setup that have basically the same type of data . I want to create another table from all the unique entries from all the entries in all the columns. 

    C1 (Name1)          C2 (name2)          C3 (name3)
R1  R1 C1 (John)        R1 C2 (Tiny Tim)    R1 C3 (Big Sally)
R2  R2 C1 (Big Sally)   R2 C2 (john)        R2 C3 (Paul)
R3  R3 C1 (Tiny Tim)    R3 C2 (paul)        R3 C3 (rajesh)
R4  R4 C1 (Normal Ned)  R4 C2 (Big Sally)   R4 C3 (Normal Ned)

This is what i need

    C1
R1  John
R2  Big Sally
R3  Tiny Tim
R4  Normal Ned
R5  Rajesh
R6  Paul

Upvotes: 0

Views: 185

Answers (3)

georgepsarakis
georgepsarakis

Reputation: 1957

The fastest way I can think is the following:

CREATE TABLE c_unique (c1 varchar(100 not null));
ALTER TABLE c_unique ADD UNIQUE INDEX (c1);
INSERT IGNORE INTO c_unique(c1) SELECT c1 FROM table;
INSERT IGNORE INTO c_unique(c1) SELECT c2 FROM table;
INSERT IGNORE INTO c_unique(c1) SELECT c3 FROM table;

Using UNION will implicitly give you DISTINCT results and perform a temporary operation with ordering in order to accomplish this. By using INSERT IGNORE, you just reject rows on the INSERT operation based on the UNIQUE index, which I assume will be much faster.

Note: Using UNIQUE on string columns will work for a specific character length. Thus you may have to specify the prefix length. You can read more on the subject here: CREATE INDEX Syntax

Upvotes: 0

user319198
user319198

Reputation:

Try below:

   insert into newtablename
   (
        select distinct  uniquenames from (
            SELECT distinct c1 as uniquenames  FROM tablename
            UNION
            SELECT distinct  c2 as uniquenames  FROM tablename
            UNION
            SELECT distinct  c3 as uniquenames  FROM tablename)
    )

Upvotes: 2

ChuyAMS
ChuyAMS

Reputation: 500

You can do it using UNION keyword.

SELECT c1 FROM table
UNION
SELECT c2 FROM table
UNION
SELECT c3 FROM table;

The results will be as you ask.

Then just get the values and insert it on the new table or you could use INSERT ... SELECT to insert quickly.

Upvotes: 2

Related Questions