Skip to main content

Data & Intelligence

SQL Magic Series – Minus Sign in ORDER BY

We are beginning a new series on SQL – A Magic SeriesWe 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; ?

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

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.
Sql Magic Series 1

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.
Sql Magic Series 1

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.
Sql Magic Series 1

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.
Sql Magic Series 1

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.

Thoughts on “SQL Magic Series – Minus Sign in ORDER BY”

  1. Good one Apeksha!!

    I think MINUS will not work if the data type of the column is string (char/varchar).

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Apeksha Singh

Apeksha has 4+ years of experience in the Business Intelligence & Analytics domain. She is working with Perficient as Senior Tableau Developer. Apeksha is enthusiastic & eager to deep dive into Data Science.

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram