CODEWITHSUNDEEP
mysqlsql
Raj
Raj

Reputation: 3061

MySQL Limit Number Of Rows Returned By Union All Statement

I need to query a dozen tables and return a UNION ALL of all of these tables. All of these tables have the same column names. The number of rows returned by this query should be a maximum of 100. 

`SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2 LIMIT 100;`

The above query causes 100 rows from table2 to be combined with all rows from table1. How can this be accomplished?

Upvotes: 1

Views: 5714

Answers (4)

Stefan Ticu
Stefan Ticu

Reputation: 2103

Try:

SELECT column1, column2 FROM ( 
  SELECT column1, column2 
  FROM table1 
 UNION ALL 
  SELECT column1, column2 
  FROM table2
) as resutl_table 
LIMIT 100;

Upvotes: 6

Shakti Singh
Shakti Singh

Reputation: 86416

SELECT * FROM  (
            SELECT column1, column2 
            FROM table1 
              UNION ALL 
            SELECT column1, column2 
            FROM table2 
            ) as result_set LIMIT 100

Upvotes: 1

mdma
mdma

Reputation: 57777

You want the limit to apply to all tables, so you "wrap" a query around your base query. The wrapped query can then apply a row count limit to all tables taken as a single result set.

SELECT * FROM ( <your existing query> ) LIMIT 100;

You could also apply an order as well if desired.

SELECT * FROM ( <your existing query> ) ORDER BY column1 LIMIT 100

Upvotes: 2

Nicola Cossu
Nicola Cossu

Reputation: 56397

select * from (
SELECT column1, column2 FROM table1 
UNION ALL 
SELECT column1, column2 FROM table2 ) as t 
LIMIT 100

Upvotes: 1

Related Questions