CODEWITHSUNDEEP
sqloracle-database
Joshua Thao
Joshua Thao

Reputation: 63

How do I combine both my top 5 salaries and bottom 5 to display in Oracle?

So I know how to get the top 5 and bottom 5 to display by themselves. Problem is how do I combine both to display at the same time. This is what I have but it only shows the bottom 5.

SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL DESC
)
WHERE ROWNUM <6 AND

SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL ASC
)
WHERE ROWNUM <6;

Upvotes: 0

Views: 269

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use UNION or UNION ALL:

SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL DESC
)
WHERE ROWNUM < 6
UNION ALL
SELECT SAL FROM
(
SELECT DISTINCT SAL FROM EMP WHERE SAL IS NOT NULL ORDER BY SAL ASC
)
WHERE ROWNUM < 6;

You can also write this more succinctly as:

SELECT SAL
FROM (SELECT DISTINCT SAL,
             DENSE_RANK() OVER (ORDER BY sal) as seqnum_asc,
             DENSE_RANK() OVER (ORDER BY sal) as seqnum_desc
      FROM EMP
      WHERE SAL IS NOT NULL
     ) s
WHERE seqnum_asc < 6 OR seqnum_desc < 6;

Upvotes: 4

Related Questions