SQL query on multiple columns and sort

I have two tables and the ZIP code is a field. If I want to get the top 10 in each table, I use the following command.

select ZIP, count(*) from tab37 group by ZIP order by count(*) desc limit 10

However, I have 3 other tables (tab38, tab39, tab40) and want to include these 3 tables also. How do I write a query to do the same (get the top 10 ZIP codes in all the 4 tables)

Answer

Union all and bracket the queries

(select ZIP, count(*) from tab37 group by ZIP order by count(*) desc limit 10)
union all
(select ZIP, count(*) from tab38 group by ZIP order by count(*) desc limit 10)
union all
(select ZIP, count(*) from tab39 group by ZIP order by count(*) desc limit 10)
union all
(select ZIP, count(*) from tab40 group by ZIP order by count(*) desc limit 10)

I am assuming that you don’t care that a zip code may be present in one or more tables – if you do care then amend your question appropriatly.