CODEWITHSUNDEEP
mysql
Kunal
Kunal

Reputation: 57

MySQL-How to select first duplicate row of corresponding column value

I have a table that contains three columns(A,B,C). All columns are of datatype varchar().Here i want to select only those initial rows which is first duplicate value of column A. Like column A has distinct value (1,2,3) so the initial row that has value 1 in column A need to be retrieved.one more thing i don't have a timestamp column in my dataset.

For value 1 in column A, i need to retrieve row that has 1,2,1 value for column (A,B,C)
For value 2 in column A, i need to retrieve row that has 2,4,1 value for column (A,B,C)
For value 3 in column A, i need to retrieve row that has 3,1,1 value for column (A,B,C)

A|B|C
-----  
1|2|1
3|1|2
3|3|1
2|4|1
3|1|2
3|1|4
1|2|3

Now i want a Output Table as describe below.

A|B|C
-----
1|2|1
3|1|2
2|4|1

Upvotes: 1

Views: 2792

Answers (1)

Strawberry
Strawberry

Reputation: 33945

Consider the following. For simplicity, I'm using an auto_incrementing id but a unique timestamp would work just as well...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table 
 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,A INT NOT NULL
 ,B INT NOT NULL
 ,C INT NOT NULL 
 ,UNIQUE(a,b,c)
 );

 INSERT INTO my_table (a,b,c) VALUES
 (1 ,2 ,1),
 (1 ,1 ,2),
 (2 ,3 ,1),
 (2 ,4 ,1),
 (3 ,1 ,2),
 (3 ,1 ,4),
 (3 ,2 ,3);

 SELECT x.* FROM my_table x ORDER BY id;
 +----+---+---+---+
 | id | A | B | C |
 +----+---+---+---+
 |  1 | 1 | 2 | 1 |
 |  2 | 1 | 1 | 2 |
 |  3 | 2 | 3 | 1 |
 |  4 | 2 | 4 | 1 |
 |  5 | 3 | 1 | 2 |
 |  6 | 3 | 1 | 4 |
 |  7 | 3 | 2 | 3 |
 +----+---+---+---+

 SELECT x.* 
   FROM my_table x 
   JOIN 
      ( SELECT a
             , MIN(id) min_id 
          FROM my_table 
         GROUP 
            BY a
      ) y 
     ON y.a = x.a 
    AND y.min_id = x.id 
  ORDER 
     BY id;
 +----+---+---+---+
 | id | A | B | C |
 +----+---+---+---+
 |  1 | 1 | 2 | 1 |
 |  3 | 2 | 3 | 1 |
 |  5 | 3 | 1 | 2 |
 +----+---+---+---+

For more information on this soution, see the manual (http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html).

Upvotes: 4

Related Questions