Essential SQL Queries collection

by

Query Collections

Duplicate value

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1
Python

Delete Duplicate Records While Keeping One

Efficiently remove duplicates but keep one copy.

WITH cte AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
    FROM your_table
)
DELETE FROM your_table WHERE id IN (SELECT id FROM cte WHERE rn > 1)
Python

Nth Largest Value

SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1 OFFSET n-1
Python

Replace query

This query will be helpful if you want to replace a part of the string in the table.

UPDATE table_name SET 'col-1' = REPLACE('col-1', 'old string', 'new-string')
Python

Detect missing values in a sequence

WITH RECURSIVE sequence_generator AS (
SELECT MIN(id) AS id FROM table_name
UNION ALL
SELECT id + 1 FROM sequence_generator
WHERE id < (SELECT MAX(id) FROM table_name)
)
SELECT sg.id AS missing_id
FROM sequence_generator sg
LEFT JOIN table_name o ON sg.id = o.id
WHERE o.id IS NULL;
Python

Sample Table

id
1
4
6

Output

id
2
3
5

Good To Know

Delete or truncate row having foreign key constraints

You should be 100% sure if you going to delete or truncate a table with a foreign key as it may break your foreign key constraints

Steps to be followed

  • Disable the foreign key check
  • Do require operations
  • Enable the foreign key check
SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;
Python

SET the logs of each query

Enabling query logs can be useful for debugging.

Time-based logs

Table-based logging means that MySQL logs queries and activities into a database table (mysql.general_log) instead of a file. This allows you to inspect queries directly within MySQL using SQL commands.

Command

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
Python

View the Logs from the table

SELECT * FROM mysql.general_log;
Python

File Based Logs

File-based logging means MySQL writes query logs to a specified file instead of storing them in a database table. This is useful when you want to analyze logs outside MySQL using text editors, log management tools, or shell commands.

SET GLOBAL log_output = "FILE"; 
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';
Python

Off

Don’t forget to off the logs, if you are not using them. keeping them on for too long may impact performance.

SET GLOBAL general_log = 'OFF';
Python

Leave a Comment