CODEWITHSUNDEEP
phpmysqlselectdistinct
Pe La
Pe La

Reputation: 47

Select multiple row without duplicate

There is a table like this:

+---------+--------+--------+
| id      | prefix | number |
+---------+--------+--------+
| 2528361 | 100#11 | 4444   |
| 2528364 | 100#13 | 4444   |
| 2528362 | 100#12 | 2222   |
| 2528363 | 100#12 | 3333   |
+---------+--------+--------+

The query is "SELECT id, prefix, number WHERE (SOME_AND_OR)"

I need to remove those fields with duplicate entries on prefix OR number like this:

+---------+--------+--------+
| id      | prefix | number |
+---------+--------+--------+
| 2528361 | 100#11 | 4444   |
| 2528362 | 100#12 | 2222   |
+---------+--------+--------+

would be much appreciated if you can help me.

Upvotes: 1

Views: 98

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

We can try to do a double aggregation, one to remove duplicates in the prefix and the other to remove duplicates in the number. First, do the aggregation on the prefix in a separate view:

CREATE VIEW yourView AS
SELECT p1.*
FROM yourTable p1
INNER JOIN
(
    SELECT prefix, MIN(number) AS min_number
    FROM yourTable
    GROUP BY prefix
) p2
    ON p1.prefix = p2.prefix AND p1.number = p2.min_number

Then, do the second aggregation on the number:

SELECT v1.*
FROM yourView v1
INNER JOIN
(
    SELECT number, MIN(prefix) AS min_prefix
    FROM yourView
    GROUP BY number
) v2
    ON v1.number = v2.number AND v1.prefix = v2.min_prefix

Note that this logic may fail if a group of records, say 3 or more, have overlapping numbers and prefixes. In this case, some information may be lost by the above query. But I still offer this as a potential answer, because your sample data does not imply that this will be happening.

Output:

enter image description here

Demo here:

Rextester

Upvotes: 1

Related Questions