How do I order rows after using "Fetch First" in Oracle SQL?

Question

I have an Oracle SQL query that uses FETCH FIRST to find employees with the highest annual salary in a PeopleSoft database.

SELECT A.EMPLID, A.ANNUAL_RT
FROM PS_EMPLOYEES A
ORDER BY A.ANNUAL_RT DESC
FETCH FIRST ROW WITH TIES;

EMPLID   ANNUAL_RT
20218    100000
10300    100000
26992    100000
17864    100000

I want to sort my results by EMPLID. However, since I'm using FETCH FIRST 1 ROW WITH TIES, adding EMPLID to my ORDER BY clause limits my results to the 1 employee with the lowest ID number.

SELECT A.EMPLID, A.ANNUAL_RT
FROM PS_EMPLOYEES A
ORDER BY A.ANNUAL_RT DESC, A.EMPLID ASC
FETCH FIRST 1 ROW WITH TIES;

EMPLID   ANNUAL_RT
10300    100000

How can I sort my results without affecting which rows are returned by FETCH FIRST?

EMPLID   ANNUAL_RT
10300    100000
17864    100000
20218    100000
26992    100000

Answer 1

Enclose your query as a table expression (a common table expression will also do). This way you can post-process it.

For example:

select *
from (
  SELECT A.EMPLID, A.ANNUAL_RT
  FROM PS_EMPLOYEES A
  ORDER BY A.ANNUAL_RT DESC
  FETCH FIRST 1 ROW WITH TIES
) x
order by emplid;

Answer 2

You can use RANK() or DENSE_RANK() instead of FETCH FIRST.

SELECT EMPLID, ANNUAL_RT
FROM (
  SELECT
    A.EMPLID,
    A.ANNUAL_RT,
    RANK() OVER (ORDER BY A.ANNUAL_RT DESC) AS RANK_NO
  FROM PS_EMPLOYEES A
)
WHERE RANK_NO = 1
ORDER BY EMPLID;

Results:

EMPLID   ANNUAL_RT
10300    100000
17864    100000
20218    100000
26992    100000