SELECT "key" , AVG(bal) FROM ( SELECT "key" , bal , ROW_NUMBER() OVER (PARTITION BY "key" ORDER BY bal ASC) AS rowasc , ROW_NUMBER() OVER (PARTITION BY "key" ORDER BY bal DESC) AS rowdesc FROM tab1 ) x WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1) GROUP BY "key" ORDER BY "key" ;