SQL Query Sorting Order – How to Define?


I learned it only today, that too after 2 hours of thinking. This probably is a readily available answer on Internet or Forums or Manuals, but it never struck my mind to check all those sources.

Issue: I wrote a SQL query and wanted to process the records in a specific sort order. Let’s say for instance, my query is:

SELECT ITEMNMBR, DOCTYPE, DOCDATE, TRXQTY 
FROM IV30300
ORDER BY ITEMNMBR, DOCTYPE, DOCDATE DESC

Please note that I have used DESC for my sorting order. Basically I wanted to sort the records based on:

1. Item Number (to be in Ascending Order)
2. Document Type (to be in Descending Order)
3. Document Date (to be in Descending Order)

The above query for my requirement, always returned records in a wrong sort order. The Sort Order was like this:

1. Item Number (Ascending Order) – which is Correct
2. Document Type (Ascending Order) – which is Wrong
3. Document Date (Descending Order) – which is Correct

The reason I learned for this, is a trivial SQL concept, which I never knew till today.

Let’s revisit my query, which is now corrected one:

SELECT ITEMNMBR, DOCTYPE, DOCDATE, TRXQTY
FROM IV30300
ORDER BY ITEMNMBR, DOCTYPE DESC, DOCDATE DESC

The above, corrected, query shows one more DESC added to column DOCTYPE. That means, if we want to sort records in Descending Order based on more than one column, we must specify DESC on each relevant column. Otherwise, it will take only the last column.
 
Till now, I was under the impression that specifying DESC in the last (once) is more than enough.
 
I thought I would share this with people, in case anyone else overlooked this simple concept, like me.
 
VAIDY

Advertisements

One thought on “SQL Query Sorting Order – How to Define?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s