--... SQL statements, possibly using other views/functions defined by you ...
with dean_serving as (
    SELECT
      p.name,
      o.longname,
      a.starting,
      a.ending,
      current_date - a.starting period
    FROM affiliations a
      JOIN staff_roles sr ON a.role = sr.id
      JOIN orgunits o ON a.orgunit = o.id
      JOIN orgunit_types ot ON ot.id = o.utype AND ot.name = 'Faculty'
      JOIN staff s ON s.id = a.staff
      JOIN people p ON p.id = s.id
    WHERE sr.name = 'Dean' AND a.ending is NULL
    ORDER BY 2 DESC
)
SELECT
  case when (period in (select max(period) from dean_serving)) then 'Longest serving'
       WHEN (period in (select min(period) from dean_serving)) then 'Shortest serving'
  end,
  name, longname, starting
from dean_serving
where period in (select max(period) from dean_serving) or period in (select min(period) from dean_serving)
;