Uso de Group by y función max

[Libras]

jugando un poco con los valores y las funciones de sql podemos sacar algo como esto:

Código SQL:

CREATE TABLE #temp
(
id_alumno INT,
nombre VARCHAR(20),
semestre INT,
promedio DECIMAL(16,10)
)
 
INSERT INTO #temp VALUES (01 ,' Juan ', 1 , 16.74)
INSERT INTO #temp VALUES (02 ,' John ', 1 , 15.07)
INSERT INTO #temp VALUES (03 ,' Pepe',1 , 14.32)
INSERT INTO #temp VALUES (04 ,' Carlos ', 2 , 12.29)
INSERT INTO #temp VALUES (05 ,' Cesar ', 2 , 12.00)
INSERT INTO #temp VALUES (06 ,' Cecilia ', 2 , 11.00)
 
SELECT t1.* FROM #temp AS t1
LEFT JOIN (SELECT MAX(promedio) promedio,semestre FROM #temp GROUP BY semestre
UNION
SELECT MIN(promedio) promedio,semestre FROM #temp GROUP BY semestre
) AS t2 ON (t1.semestre=t2.semestre AND t1.promedio=t2.promedio)
WHERE t2.promedio IS NOT NULL