CODEWITHSUNDEEP
mysql
user3299199
user3299199

Reputation: 63

How not to SELECT in MySQL duplicates of rows

I have MySQL table as follow:

| id | seller | type |
|  1 |  345   |  AB  |
|  3 |  234   |  CD  |
|  7 |  345   |  AB  |
| 10 |  234   |  AB  |

In this example, I have duplicates of id 1 and 7 and I need to return only one of them. So I am running following query:

SELECT * 
FROM table1 
ORDER BY seller, type ASC;

Which gives me table:

| id | seller | type |
| 10 |  234   |  AB  |
|  3 |  234   |  CD  |
|  1 |  345   |  AB  |
|  7 |  345   |  AB  |

When I run following query with GROUP BY type:

SELECT *
FROM (
    SELECT * 
    FROM table1 
    ORDER BY seller, type ASC
     ) AS T1
GROUP BY T1.type;

I am going to get only two records.

My solution would be to create additional column with "unique_ID" which would be combination of seller and type and then GROUP BY unique_ID. But I was wondering if there is another approach.

Thanks.

Upvotes: 1

Views: 48

Answers (2)

Nikhil K
Nikhil K

Reputation: 54

Please try this 

SELECT * FROM `table1` group by `seller` , `type`

It's output will be :-

    +----+--------+------+
    | id | seller | type |
    +----+--------+------+
    | 10 |  234   |  AB  |
    |  3 |  234   |  CD  |
    |  1 |  345   |  AB  |
    +----+--------+------+

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this :

SELECT MIN(id),seller, type  
FROM table1 
GROUP BY seller, type

Upvotes: 1

Related Questions