Union Distinct rows but order them by number of occurrences in mysql

Question

Hi I have the following query:

SELECT o.id,o.name FROM object o 
WHERE 
    (
        o.description LIKE '%Black%' OR
        o.name LIKE '%Black%'
    ) 
UNION ALL
SELECT o2.id,o2.name FROM object o2
WHERE 
    (
        o2.description LIKE '%iPhone%' OR
        o2.name LIKE '%iPhone%'
    ) 

Which procude the following:

id  name
2   New Black iPhone
1   New White iPhone
2   New Black iPhone

I would like to UNION DISTINCT, but I would also like the result ordered by the number of occurrences of each identical row (primary: id).

Answer 1

you can group your result on id and name columns and see how many occurences each distinct value has. then you can order them by most occurences appearing first order by count(*) desc

select id, name, count(*) as occurences
from (
    SELECT o.id,o.name FROM object o 
    WHERE 
        (
            o.description LIKE '%Black%' OR
            o.name LIKE '%Black%'
        ) 
    UNION ALL
    SELECT o2.id,o2.name FROM object o2
    WHERE 
        (
            o2.description LIKE '%iPhone%' OR
            o2.name LIKE '%iPhone%'
        ) 
)
group by id, name
order by count(*) desc

this will return

id  name              occurences
2   New Black iPhone  2
1   New White iPhone  1