Limit total number of results across tables in MySQL?

For this, you can use UNION ALL along with LIMIT concept. For our example, we will create three tables.

Let us create the first table −

mysql> create table demo3
−> (
−> value int
−> );
Query OK, 0 rows affected (1.39 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo3 values(10);
Query OK, 1 row affected (0.13 sec)

mysql> insert into demo3 values(20);
Query OK, 1 row affected (0.08 sec)

mysql> insert into demo3 values(30);
Query OK, 1 row affected (0.08 sec)

Display records from the table using select statement −

mysql> select *from demo3;

This will produce the following output −

+-------+
| value |
+-------+
| 10    |
| 20    |
| 30    |
+-------+
3 rows in set (0.00 sec)

The query to create second table is as follows −

mysql> create table demo4
−> (
−> value1 int
−> );
Query OK, 0 rows affected (2.10 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo4 values(40);
Query OK, 1 row affected (0.10 sec)

mysql> insert into demo4 values(10);
Query OK, 1 row affected (0.09 sec)

mysql> insert into demo4 values(60);
Query OK, 1 row affected (0.17 sec)

Display records from the table using select statement −

mysql> select *from demo4;

This will produce the following output −

+--------+
| value1 |
+--------+
| 40     |
| 10     |
| 60     |
+--------+
3 rows in set (0.00 sec)

Here is the query to create third table.

mysql> create table demo5
−> (
−> value2 int
−> );
Query OK, 0 rows affected (2.13 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo5 values(100);
Query OK, 1 row affected (0.09 sec)
mysql> insert into demo5 values(60);
Query OK, 1 row affected (0.14 sec)
mysql> insert into demo5 values(50);
Query OK, 1 row affected (0.10 sec)

Display records from the table using select statement −

mysql> select *from demo5;

This will produce the following output −

+--------+
| value2 |
+--------+
| 100    |
| 60     |
| 50     |
+--------+
3 rows in set (0.00 sec)

Following is the query to limit total number of results across tables −

mysql> select tbl.*
−> from ((select * from demo3 ORDER BY value DESC LIMIT 2
−> ) UNION ALL
−> (select * from demo4 ORDER BY value1 DESC LIMIT 2
−> ) UNION ALL
−> (select * from demo5 ORDER BY value2 DESC LIMIT 2
−> )
−> ) tbl
−>
−> LIMIT 2;

This will produce the following output −

+-------+
| value |
+-------+
| 30    |
| 20    |
+-------+
2 rows in set (0.00 sec)

AmitDiwan

Updated on 19-Nov-2020 10:48:20

• Related Questions & Answers
• Finding total number of rows of tables across multiple databases in MySQL?
• Get total number of rows while using LIMIT in MySQL?
• Limit length of longtext field in MySQL SELECT results?
• Total number of fields in all tables in database?
• How to limit the number of results returned from grep in Linux?
• How can I sum columns across multiple tables in MySQL?
• How to limit records to only the last five results in MySQL
• How to count the total number of tables in a page in Selenium with python?
• Get a count of total documents with MongoDB while using limit?
• Can we get total number of rows in a MySQL database?
• MongoDB query to insert but limit the total records
• How to count the number of tables in a MySQL database?
• Get a fixed number of results in descending order using a MySQL query
• Total number of longest increasing sequences in JavaScript
• Limit number of values in a field using MongoDB?