We are beginning a new series on SQL – A Magic Series. We will see few of many, yet effective tricks & solutions to make SQL easy in daily use. Let’s begin!!
So, the Question we are dealing here is:
What does the SQL minus sign (-) mean in ORDER BY -emp_no DESC; ?
Before answering that, let’s see, how ORDER BY works without MINUS:
- ORDER BY emp_no DESC ;
If you end the relevant query this way, you will obtain an output ordered with the highest employee number on top, the lowest employee number down the list, and the null values at the end.
the top part of the output has been displayed
- ORDER BY emp_no ASC ;
This ending of the query will do the opposite – the null values will be on top, and then the employee numbers will grow from the lowest to the highest.
the top part of the output has been displayed
And now, what changes with MINUS:
- ORDER BY -emp_no DESC ;
Using this code, we will first order the employees from the lowest to the highest number, and then leave the null values at the end.
the top part of the output has been displayed
- ORDER BY -emp_no ASC ;
Following the logic explained so far, this ending would list the null value first, and will then order all employees from the highest to the lowest number.
the top part of the output has been displayed
Depending on the situation, we may choose between the four SQL clause.
Why would using a minus sign in such a situation be useful at all?
Specifically, the combination used in “ORDER BY -emp_no DESC;” is a frequently used technique as it allows the user to sort their output in descending order, without starting with a (sometimes large) number of null values.
In other words, if the user prefers to see the null values at the end of the output, using ORDER BY -a.emp_no DESC; is a very convenient choice.
Read my previous blogs here.
Good one Apeksha!!
I think MINUS will not work if the data type of the column is string (char/varchar).
Nice, Looking forward for upcoming series !