SQL --Uso de HAVING en self-joins

Uso de HAVING en self-joins

Esto está explicado en el librito Optimizing Transact-SQL, pero vale la pena comentarlo:

Supongamos que tengo una tabla con una columna numérica, y quiero el máximo que es menor a un número K dado: la solución obvia es:

select max(foo) from bar where foo < K

pero no sirve si quiero traer otras columnas.

Para fijar ideas, supongamos que tengo la tabla Comisiones con campos Nivel y Porcentaje, y la consulta típica es: seleccionar el Porcentaje de Comisiones para el máximo Nivel (notar cómo la condicion establece un piso) menor a un K dado. La segunda solución obvia es:

select Porcentaje from Comisiones
where Nivel = (select max(Nivel) from Comisiones where Nivel < K)

El truco para eliminar el select anidado es este: el where filtra filas, y el having filtra grupos. Pero no hace falta tener grupos; la tabla completa es, conceptualmente, un grupo. Entonces, esta consulta hace lo mismo:

select Porcentaje from Comisiones
where Nivel < K
having Nivel = max(Nivel)

Cuidado, porque si tengo más condiciones, tengo que pensar qué condiciones corresponden a la fila y qué condiciones corresponden al grupo. Por ejemplo, supongamos que la tabla además tiene una columna Plan, que puede ser 1, 2 ó 3. Si quiero seleccionar el Porcentaje de Comisiones para el Plan 3 con Nivel menor a K, la condición Plan < 3 va en el having, y no en el where:

select Porcentaje from Comisiones
where Nivel < K
having Plan = 3
and Nivel = max(Nivel)

Alternativamente, puedo agrupar explícitamente:

select Porcentaje from Comisiones
where Plan = 3
and Nivel < K
group by Plan
having Nivel = max(Nivel)

Buscar techos es el problema inverso: es el mínimo Nivel mayor a uno dado.