MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.18


CREATE TABLE prescription (
p_name char(50),
lab    char(50),
doctor_VAT char(50),
date_timestamp  datetime, 
ID             bigint,
dosage        char(50),
prescription_description char(50));
insert into prescription values ('test1','lab1','','2019-11-01', 1, '', '');
insert into prescription values ('test1','lab1','','2019-11-02', 1, '', '');
insert into prescription values ('test1','lab1','','2019-11-03', 1, '', '');
insert into prescription values ('test2','lab1','','2019-11-04', 1, '', '');
insert into prescription values ('test2','lab1','','2019-11-05', 1, '', '');
insert into prescription values ('test3','lab1','','2019-11-06', 1, '', '');

insert into prescription values ('test1','lab1','','2019-11-01', 2, '', '');
insert into prescription values ('test2','lab1','','2019-11-02', 2, '', '');
insert into prescription values ('test2','lab1','','2019-11-03', 2, '', '');
insert into prescription values ('test3','lab1','','2019-11-04', 2, '', '');
insert into prescription values ('test3','lab1','','2019-11-05', 2, '', '');
insert into prescription values ('test3','lab1','','2019-11-06', 2, '', '');
SELECT * 
FROM prescription;


p_name
lab
doctor_VAT
date_timestamp
ID
dosage
prescription_description


test1
lab1

2019-11-01 00:00:00
1



test1
lab1

2019-11-02 00:00:00
1



test1
lab1

2019-11-03 00:00:00
1



test2
lab1

2019-11-04 00:00:00
1



test2
lab1

2019-11-05 00:00:00
1



test3
lab1

2019-11-06 00:00:00
1



test1
lab1

2019-11-01 00:00:00
2



test2
lab1

2019-11-02 00:00:00
2



test2
lab1

2019-11-03 00:00:00
2



test3
lab1

2019-11-04 00:00:00
2



test3
lab1

2019-11-05 00:00:00
2



test3
lab1

2019-11-06 00:00:00
2




SELECT 
   p1.ID,
   (SELECT p2.p_name 
    FROM prescription AS p2 
    WHERE p2.ID=p1.ID
    GROUP BY p2.p_name
    ORDER BY count(*) DESC
    LIMIT 1) as MostUsed
FROM prescription AS p1
GROUP BY p1.ID


ID
MostUsed


1
test1

2
test3

p_name	lab	date_timestamp	ID
test1	lab1	2019-11-01 00:00:00	1
test1	lab1	2019-11-02 00:00:00	1
test1	lab1	2019-11-03 00:00:00	1
test2	lab1	2019-11-04 00:00:00	1
test2	lab1	2019-11-05 00:00:00	1
test3	lab1	2019-11-06 00:00:00	1
test1	lab1	2019-11-01 00:00:00	2
test2	lab1	2019-11-02 00:00:00	2
test2	lab1	2019-11-03 00:00:00	2
test3	lab1	2019-11-04 00:00:00	2
test3	lab1	2019-11-05 00:00:00	2
test3	lab1	2019-11-06 00:00:00	2