php - MySql Tag System - How to select just one Tag for each Id, without repeating the same Tag?
I'm implementing a Tag system using PHP and MySql.
In my Tags table there are two columns (Id and Tag). Each Id may have one or more tag(s) associated to it - like this:
| Id | Tag |
|---|---|
| 1 | red |
| 1 | blue |
| 1 | orange |
| 2 | green |
| 3 | black |
| 4 | grey |
| 5 | grey |
| 6 | pink |
| 7 | white |
| 7 | purple |
| 8 | blue |
| 9 | yellow |
| 9 | cyan |
Answer
Solution:
Use NOT EXISTS to filter out duplicate tags and aggregate:
SELECT t1.Id, MAX(t1.Tag) Tag
FROM Tags t1
WHERE NOT EXISTS (
SELECT 1
FROM Tags t2
WHERE t2.Id > t1.Id AND t2.Tag = t1.Tag
)
GROUP BY t1.Id
ORDER BY t1.Id DESC
Instead of MAX() you could use MIN() or
See the demo.
Answer
Solution:
Create a temp table with unique constraint and do insert ignore.
DROP Temporary table IF exists T_Test;
create Temporary table T_Test(id INTEGER UNIQUE, tag VARCHAR(10) UNIQUE);
insert IGNORE into T_Test
SELECT * FROM Tags
ORDER BY id DESC, tag DESC;
SELECT * from T_Test;