tk66 tk66 - 1 month ago 5
MySQL Question

Sort by number of occurrence in tables

I have a database with many tables.. and each table has stored only IDs. Now what I want to do is:

SELECT id FROM table1, table2, table3
GROUP BY id;


but I also want to sort them by decreasing order of occurrence.

For example the IDs that are in all 3 tables should appear on top and the IDs appearing in only one table should be at the bottom. Any clue on how to do this?

Answer

Try this too

select id from
(
    SELECT id FROM table1
    union all
    select id from table2
    union all
    select id from table3 
) as t
GROUP BY id
order by count(id) desc
Comments