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