Hi All,
I am using MySQL database.
Table - emp
Columns :-
job varchar(20)
ename varchar(50)
residency varchar(50)
Data :-
Job ename Residency
Doctor doc_ename1 Rented House
Doctor doc_ename2 Rented House
Doctor doc_ename3 Rented House
Doctor doc_ename4 Rented House
Doctor doc_ename5 Rented House
Doctor doc_ename6 Own House
Doctor doc_ename7 Own House
Doctor doc_ename8 Own House
Doctor doc_ename9 Office Residency
Doctor doc_ename10 Office Residency
Nurse nur_ename1 Own House
Nurse nur_ename2 Own House
Nurse nur_ename3 Own House
Nurse nur_ename4 Own House
Nurse nur_ename5 Own House
Nurse nur_ename6 Rented House
Nurse nur_ename7 Rented House
Nurse nur_ename8 Rented House
Nurse nur_ename9 Office Residency
Nurse nur_ename10 Office Residency
how to find the residency which is not used mostly in each job
The result should be like below
Job Residency
Doctor Own House
Doctor Office Residency
Nurse Rented House
Nurse Office Residency
How to write a query for this.
Thanks in advance.
Regards,
Abu