Press "Enter" to skip to content

Conditional Sorting and Sorting By Function Problem

Conditional Sorting and Sorting By Function Problem

While querying some data, you need to sort by an optional value, and where that value is not present,
you’d like to change the sorting condition to another column.

Solution

Oracle supports the use of almost all of its expressions and functions in the

ORDER BY clause. This

includes the ability to use the

CASE statement and simple and complex functions like arithmetic

operators to dynamically control ordering. For our recipe, we’ll tackle a situation where we want to show
employees ordered by highest-paid to lowest-paid.
For those with a commission, we want to assume the commission is earned but don’t want to
actually calculate and show this value; we simply want to order on the implied result. The following SQL
leverages the

CASE statement in the ORDER BY clause to conditionally branch sorting logic for those with

and without a

COMMISSION_PCT value.

select employee_id, last_name, salary, commission_pct
from hr.employees
order by
case
when commission_pct is null then salary
else salary * (1+commission_pct)
end desc;

We can see from just the first few rows of results how the conditional branching while sorting has
worked.

EMPLOYEE_ID LAST_NAME SALARY COMMISSION_PCT
———– ——— —— ————–
100 King 24000
145 Russell 14000 .4
146 Partners 13500 .3
101 Kochhar 17000
102 De Haan 7000

Even though employees 101 and 102 have a higher base salary, the

ORDER BY clause using CASE hascorrectly positioned employees 145 and 146 based on their included commission percentage.

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *