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

Published on 19-Nov-2020 10:43:20

Previous Page Print Page