Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

-

This one is little tricky problem. I tried to use subquery approach to solve this problem.

 +----+------------------+

| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

Note:

Your output is the whole Person table after executing your sql. Use delete statement.

Solution:

DELETE FROM Person
WHERE Id NOT IN (SELECT p.id
FROM (
SELECT MIN(Id) as id
FROM Person
GROUP BY Email) as p);


Comments

Post a Comment

Popular posts from this blog

Priority Scheduling Algorithm Java Program.

-
import java.util.Scanner; public class priority{ public static void main(String args[]) { Scanner s = new Scanner(System.in); int x,n,p[],pp[],bt[],w[],t[],awt,atat,i; p = new int[10]; pp = new int[10]; bt = new int[10]; w = new int[10]; t = new int[10]; //n is number of process //p is process //pp is process priority //bt is process burst time //w is wait time // t is turnaround time //awt is average waiting time //atat is average turnaround time System.out.print("Enter the number of process : "); n = s.nextInt(); System.out.print("\n\t Enter burst time : time priorities \n"); for(i=0;i<n;i++) { System.out.print("\nProcess["+(i+1)+"]:"); bt[i] = s.nextInt(); pp[i] = s.nextInt(); p[i]=i+1; } //sorting on the basis of priority for(i=0;i<n-1;i++) { for...
Read more

Implement UNIX system calls like ps, fork, join, exec family, and wait for process management (use shell script/ Java/ C programming).

-
//A small example to show how to use execvp() function in C. We will have two .C files , EXEC.c and execDemo.c and we will replace the execDemo.c with EXEC.c by calling execvp() function in execDemo.c . //EXEC.c  #include<stdio.h>  #include<unistd.h>  int main()   {        int i;       printf("This  is EXEC.c, called by execvp() ");        printf("\n");       return 0;   }  Now,create an executable file of EXEC.c using command  gcc EXEC.c -o EXEC //execDemo.c #include<stdio.h> #include<stdlib.h>  #include<unistd.h>  int main()  {     //A null terminated array of character      //pointers       char *args[]={"./EXEC",NULL};       execvp(args[0],args);     /*All statements are ignored after execvp() call as this whole process(execDemo.c) is replaced by ...
Read more

Implement a class CppArray which is identical to a one-dimensional C++ array (i.e., the index set is a set of consecutive integers starting at 0) except for the following : 1. It performs range checking. 2.It allows one to be assigned to another array through the use of assignment operator. 3.It supports a function that returns the size of the array.

-
#include<iostream> #include<math.h> using namespace std; class array {      int a[10],b[10],i,j,temp,n;           public:             void accept();             void display();             void range();             void sort();             void exchange();             int size(); }; void array::accept() {     cout<<"How many Elements you want to insert into the array=\n";     cin>>n;     cout<<"Enter the array Elements=\n";     for(i=0;i<n;i++)        {             cout<<...
Read more