Member Avatar for flashyflashy

I have multiple tables, table_a, table_b, table_c. Each having the same structure. i.e ID, title, rating, content. I'm using

(select * from table_a order by rating DESC) union (select * from table_b order by rating DESC)  union (select * from table_c order by rating DESC)

to list all titles from a,b,c tables. But it only lists tables one after the other.

How can I list top ten rated titles from all the tables.

  • 5 Contributors
  • 4 Replies
  • 112 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by ebookfinder

Dani AI

Generated

A few clarifications and a safe, practical way to get the top 10 across those same-structure tables.

As showed, the rows must be combined first and sorted afterward. was right that the final ORDER BY (not per-SELECT) controls the overall ranking. A compact, production-ready pattern is to UNION the selects (use UNION ALL if duplicates are acceptable) and then sort and limit the combined result. For example:

SELECT id, title, rating, content, 'a' AS src FROM table_a
UNION ALL
SELECT id, title, rating, content, 'b' AS src FROM table_b
UNION ALL
SELECT id, title, rating, content, 'c' AS src FROM table_c
ORDER BY rating DESC
LIMIT 10;

Why UNION ALL vs UNION: UNION removes duplicates and costs extra work; UNION ALL is faster if de-duplication is not needed. See the MySQL docs on UNION for details: MySQL UNION documentation.

If the tables are large and you want better performance, fetch a small top set from each table first, then union those and take the top 10. This limits the amount of rows that need sorting:

(SELECT id, title, rating, content FROM table_a ORDER BY rating DESC LIMIT 20)
UNION ALL
(SELECT id, title, rating, content FROM table_b ORDER BY rating DESC LIMIT 20)
UNION ALL
(SELECT id, title, rating, content FROM table_c ORDER BY rating DESC LIMIT 20)
ORDER BY rating DESC
LIMIT 10;

Notes and cautions: suggestion to add LIMIT per-query can be adapted this way, but choosing the per-table LIMIT requires judgment (make it comfortably larger than 10). ’s cross-join approach is wrong here — it produces Cartesian products. Make sure all SELECTs return the same columns/types (column names come from the first SELECT), index rating if possible, and consider consolidating these tables into one with a source column for simpler, faster queries. For query planning tips, see MySQL query optimization: .

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

add limit 10 to each query

Member Avatar for shasank 
select a.title, b.title, c.title from table_a a, table_b b, table_c c where a.rating > 10 and b.rating > 10 and c.rating > 10;

....

Hope this helps you....
Shasank

Member Avatar for cgyrob

I know this thread is a few days old but if you want the top 10 from the combined list you just sort the list after the unions not for each table.

(SELECT * from table_a )
union (SELECT * FROM table_b)  
union (SELECT * FROM table_c ) 
order by rating DESC
Limit 10
Member Avatar for ebookfinder

I know this thread is a few days old but if you want the top 10 from the combined list you just sort the list after the unions not for each table.

(SELECT * from table_a )
union (SELECT * FROM table_b)  
union (SELECT * FROM table_c ) 
order by rating DESC
Limit 10

great!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.