MySQL Top Queries
Top MySQL queries: pagination with LIMIT and OFFSET, Nth highest value, find and delete duplicate rows, and count the tables in a database.
MySQL – Count the number of tables in the database
This query counts how many tables a database has. It reads the system table INFORMATION_SCHEMA.TABLES, which keeps the details of every table in the server.
SELECT COUNT(*) AS total_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase';
Change mydatabase to the name of your database. If the database has 5 tables, the result is:
| total_tables |
|---|
| 5 |
List the table names too
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase'
ORDER BY TABLE_NAME;
Or, if you have already selected the database with USE, the short way is:
SHOW TABLES;
Related: MySQL SELECT and Aggregate functions.
MySQL Find Duplicate Records
Duplicate rows are a common problem, for example the same email saved two times. This query finds them. We use a users table where the email should be unique, but it is not.
| id | name | |
|---|---|---|
| 1 | Ravi | ravi@mail.com |
| 2 | Sita | sita@mail.com |
| 3 | Amit | ravi@mail.com |
| 4 | Neha | neha@mail.com |
| 5 | Sita K | sita@mail.com |
Find the duplicate emails
SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
| total | |
|---|---|
| ravi@mail.com | 2 |
| sita@mail.com | 2 |
Show the full duplicate rows
SELECT *
FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1
)
ORDER BY email;
This shows rows 1, 3, 2 and 5, so you can see which records are repeated.
Related: GROUP BY and HAVING and Subqueries.
MySQL Delete Duplicate Rows
This code removes duplicate rows and keeps the first one (the lowest id). We use the same users table from the find duplicates code.
| id | name | |
|---|---|---|
| 1 | Ravi | ravi@mail.com |
| 2 | Sita | sita@mail.com |
| 3 | Amit | ravi@mail.com |
| 4 | Neha | neha@mail.com |
| 5 | Sita K | sita@mail.com |
Step 1: check what will be deleted
SELECT u1.*
FROM users u1
JOIN users u2
ON u1.email = u2.email AND u1.id > u2.id;
This shows rows 3 and 5. These are the extra copies.
Step 2: delete them
DELETE u1
FROM users u1
JOIN users u2
ON u1.email = u2.email AND u1.id > u2.id;
Now the table has only one row for each email:
| id | name | |
|---|---|---|
| 1 | Ravi | ravi@mail.com |
| 2 | Sita | sita@mail.com |
| 4 | Neha | neha@mail.com |
Related: MySQL DELETE, MySQL JOINS and Constraints.
MySQL Nth Highest Value
A common interview question: find the 2nd, 3rd or Nth highest marks. We use the students table with the marks 85, 92, 78, 88, 65 and 45.
Third highest marks
SELECT DISTINCT marks
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 2;
| marks |
|---|
| 85 |
For the Nth highest value, use OFFSET N-1. So the 3rd highest uses OFFSET 2, the 2nd highest uses OFFSET 1. DISTINCT makes sure that same marks are counted one time.
Get the student with that marks
SELECT name, marks
FROM students
WHERE marks = (
SELECT DISTINCT marks
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 2
);
| name | marks |
|---|---|
| Robin | 85 |
Related: MySQL LIMIT, DISTINCT and Subqueries.
MySQL Pagination Query with LIMIT and OFFSET
Pagination means showing the records in small pages, like 10 results on each page. In MySQL we do it with LIMIT and OFFSET.
Formula
OFFSET = (page_number - 1) * records_per_page
Example: page 2, 2 students on each page
SELECT name, city, marks
FROM students
ORDER BY marks DESC
LIMIT 2 OFFSET 2;
The students ordered by marks are Maria (92), Asha (88), Robin (85), John (78), Vikram (65) and Neha (45). Page 1 has the first two, so page 2 skips two rows and shows the next two:
| name | city | marks |
|---|---|---|
| Robin | Delhi | 85 |
| John | Pune | 78 |
Short form
SELECT name, city, marks
FROM students
ORDER BY marks DESC
LIMIT 2, 2; -- LIMIT offset, row_count
Related: MySQL LIMIT and ORDER BY.