- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- 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?
- How to count the number of tables in a MySQL database?
- Get a fixed number of results in descending order using a MySQL query
- Can we get total number of rows in a MySQL database?
- Get a count of total documents with MongoDB while using limit?
- MongoDB query to insert but limit the total records
- What is the MySQL query to display the number of tables in a database?
- How can we see MySQL temporary tables in the list of tables?
