SQL Min and Max Without Subqueries

MediumSQL · PostgreSQL

Problem

GEP's people analytics team needs a quick salary-extremes report for its workforce data. Write a PostgreSQL query to identify every employee whose non-null salary is either the highest or lowest in the employees table, without using subqueries or CTEs.

Requirements

Return the employee ID, employee name, salary, and a salary_type label.
Label rows as Highest or Lowest.
Return all employees tied at either salary extreme.
Exclude employees whose salary is NULL, and order the result from highest salary to lowest salary.

Output details

Return columns named employee_id, employee_name, salary, and salary_type, in that order.
Order equal salaries by employee_id ascending.
Return each resulting employee-label combination only once.

Schema

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department	VARCHAR(80)	GEP department
salary	NUMERIC(12,2)	Annual employee salary

Tablesemployees

Requirements

Return the employee ID, employee name, salary, and a salary_type label.
Label rows as Highest or Lowest.
Return all employees tied at either salary extreme.
Exclude employees whose salary is NULL, and order the result from highest salary to lowest salary.

Output details

Return columns named employee_id, employee_name, salary, and salary_type, in that order.
Order equal salaries by employee_id ascending.
Return each resulting employee-label combination only once.