What is the smallest and largest individual for Charge Hour for Job Class?

I need to answer this tricky question in SQL, I tried many ways and finally got this query which retrieves only MAX SUM or MIN SUM without the respective employee.

This code retrieves only the MAX charge SUM values but I need it with MAX SUM value (group by job code) with the employee ID or Name as request in the question.

select MAX(t.SUM_CHARGE)
from
(select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019,a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.EMP_NUM2019)t
group by t.JOB_CODE2019 ;

here is the table create and insert statement.

create table JOB2019(
JOB_CODE2019 int,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 decimal(4,2),
PRIMARY KEY(JOB_CODE2019 )
);

create table EMPLOYEE2019(
EMP_NUM2019 int,
EMP_LNAME2019 varchar(20),
EMP_FNAME2019 varchar(20),
EMP_INITIAL2019 varchar(1),
EMP_HIREDATE2019 varchar(50),
JOB_CODE2019 int,
primary key (EMP_NUM2019),
foreign key (JOB_CODE2019) references JOB2019(JOB_CODE2019)
);

create table PROJECT2019(
PROJ_NUM2019 int,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 int,
primary key (PROJ_NUM2019),
foreign key (EMP_NUM2019) references employee2019(EMP_NUM2019)
);

create table ASSIGNMENT2019(
ASSIGN_NUM2019 int,
ASSIGN_DATE2019 varchar(50),
PROJ_NUM2019 int,
EMP_NUM2019 int,
ASSIGN_HOURS2019 decimal(5,2),
ASSIGN_CHG_HOUR2019 decimal(5,2),
ASSIGN_CHARGE2019 decimal(7,2) ,
primary key (ASSIGN_NUM2019),
foreign key (PROJ_NUM2019) references project2019(PROJ_NUM2019),
foreign key (EMP_NUM2019) references employee2019(EMP_NUM2019)
);                                                                                    

insert into JOB2019
values (500,'Programmer',35.75)
,(501,'Systems Analyst',96.75)
,(502,'Database Designer',105.00)
,(503,'Electrical Engineer',84.50)
,(504,'Mechanical Engineer',67.90)
,(505,'Civil Engineer',55.78)
,(506,'Clerical Support',26.87)
,(507,'DSS Analyst',45.95)
,(508,'Applications Designer',48.10)
,(509,'Bio Technician',34.55)
,(510,'General Support',18.36);


insert into EMPLOYEE2019
values (101,'News','John','G','08Nov2000'd,502)
,(102,'Senior','David','H','12Jun1989'd,501)
,(103,'Arbough','June','E','01Dec1997'd,503)
,(104,'Ramoras','Anne','K','15Nov1988'd,501)
,(105,'Johnson','Alice','K','01Feb1994'd,502)
,(106,'Smithfield','William',' ','22Jun2005'd,500)
,(107,'Alonzo','Maria','D','10Oct1994'd,500)
,(108,'Washington','Ralph','B','22Aug1889'd,501)
,(109,'Smith','Larry','W','18Jul1999'd,501)
,(110,'Olenko','Gerald','A','11Dec1996'd,505)
,(111,'Wabash','Geoff','B','04Apr1989'd,506)
,(112,'Smithson','Darlene','M','23Oct1995'd,507)
,(113,'Joenbrood','Delbert','K','15Nov1994'd,508)
,(114,'Jones','Annelise',' ','20Aug1991'd,508)
,(115,'Bawangi','Travis','B','25Jan1990'd,501)
,(116,'Pratt','Gerald','L','05Mar1995'd,510)
,(117,'Williamson','Angie','H','19Jun1994'd,509)
,(118,'Frommer','James','J','04Jan2006'd,510);

insert INTO PROJECT2019
values (15,'Evergreen',105)
,(18,'Amber Wave',104)
,(22,'Rolling Tide',113)
,(25,'Starflight',101); 

insert into ASSIGNMENT2019(ASSIGN_NUM2019,ASSIGN_DATE2019,PROJ_NUM2019,EMP_NUM2019,ASSIGN_HOURS2019,ASSIGN_CHG_HOUR2019)
values(1001,'04Mar2012'd,15,103,2.6,84.50)
,(1002,'04Mar2012'd,18,118,1.4,18.36)
,(1003,'05Mar2012'd,15,101,3.6,105.00)
,(1004,'05Mar2012'd,22,113,2.5,48.10)
,(1005,'05Mar2012'd,15,103,1.9,84.50)
,(1006,'05Mar2012'd,25,115,4.2,96.75)
,(1007,'05Mar2012'd,22,105,5.2,105.00)
,(1008,'05Mar2012'd,25,101,1.7,105.00)
,(1009,'05Mar2012'd,15,105,2.0,105.00)
,(1010,'06Mar2012'd,15,102,3.8,96.75)
,(1011,'06Mar2012'd,22,104,2.6,96.75)
,(1012,'06Mar2012'd,15,101,2.3,105.00)
,(1013,'06Mar2012'd,25,114,1.8,48.10)
,(1014,'06Mar2012'd,22,111,4.0,26.87)
,(1015,'06Mar2012'd,25,114,3.4,48.10)
,(1016,'06Mar2012'd,18,112,1.2,45.95)
,(1017,'06Mar2012'd,18,118,2.0,18.36)
,(1018,'06Mar2012'd,18,104,2.6,96.75)
,(1019,'06Mar2012'd,15,103,3.0,84.50)
,(1020,'07Mar2012'd,22,105,2.7,105.00)
,(1021,'08Mar2012'd,25,108,4.2,96.75)
,(1022,'07Mar2012'd,25,114,5.8,48.10)
,(1023,'07Mar2012'd,22,106,2.4,35.75);

Update assignment2019
set ASSIGN_CHARGE2019 = ASSIGN_HOURS2019 * ASSIGN_CHG_HOUR2019;

I highly appreciate your help to solve the above question. Thank you.

Answer

You want help, so you also have to make the effort to present a mre see Why should I provide a Minimal Reproducible Example for a very simple SQL query?

This what you want gives a very ugly query

SELECT t2.JOB_CODE2019,maxcharge,MIN(CONCAT(t1.EMP_FNAME2019,' ' ,t1.EMP_LNAME2019)) empnamemax,mincharge,
MIN(CONCAT(t3.EMP_FNAME2019,' ' ,t3.EMP_LNAME2019)) empnamemin
FROM
(select t.JOB_CODE2019,MAX(t.SUM_CHARGE) maxcharge,MIN(t.SUM_CHARGE) mincharge
from
(select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019)t
group by t.JOB_CODE2019) t2
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t1 
     ON t2.JOB_CODE2019 = t1.JOB_CODE2019 AND t2.maxcharge = t1.SUM_CHARGE
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
     from ASSIGNMENT2019 a inner join EMPLOYEE2019 e 
     on a.EMP_NUM2019 = e.EMP_NUM2019
     inner join JOB2019 j
     on  e.JOB_CODE2019 = j.JOB_CODE2019
     group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t3 
     ON t2.JOB_CODE2019 = t3.JOB_CODE2019 AND t2.mincharge = t3.SUM_CHARGE     
 GROUP BY t2.JOB_CODE2019;

JOB_CODE2019 | maxcharge | empnamemax         | mincharge | empnamemin        
-----------: | --------: | :----------------- | --------: | :-----------------
         503 |    633.75 | June Arbough       |    633.75 | June Arbough      
         510 |     62.42 | James Frommer      |     62.42 | James Frommer     
         501 |    406.35 | Ralph Washington   |    251.55 | Anne Ramoras      
         502 |    829.50 | Alice Johnson      |    178.50 | John News         
         508 |    529.10 | Annelise Jones     |    120.25 | elbert Joenbrood  
         506 |    107.48 | Geoff Wabash       |    107.48 | Geoff Wabash      
         507 |     55.14 | arlene Smithson    |     55.14 | arlene Smithson   
         500 |     85.80 | William Smithfield |     85.80 | William Smithfield

db<>fiddle here