Delete Multiple Entries Of Groceries

  

Write an SQL query to delete the multiple entries of the groceries from the table.

Note :- If the value is repeated multiple times, keep the row with the smallest ID. 

Input :-

Groceries table :-
ID is the Primary Key column for the Groceries table.

ID	Fruits
1	Apple
2	Orange
3	Apple
4	Mango

Output :-

ID	Fruits
1	Apple
2	Orange
4	Mango

Query :-

delete g1 
from Groceries g1, Groceries g2
where g1.Fruits=g2.Fruits and g1.id>g2.id;

Explanation : - 

This query is used to delete duplicate records from the "Groceries". The query is using a self-join by selecting from the "Groceries" table twice, with the aliases "g1" and "g2" respectively.

The delete statement is used to delete the duplicate records that were identified by the join condition. This means that for any two records with the same value in the "Fruits" column, only the one with the lower "ID" value will be retained, and the other one will be deleted.

Overall, this query is useful for removing duplicate records from the "Groceries" table, ensuring that each record is unique based on the combination of the "Fruits" and "ID" columns.