Dose multiple ORDER BY values is as effective as in an individual ORDER BY?-CodePudding

I am a junior in a sub-position to the company's DBA employee. (ORACLE, PL/SQL Developer IDE)

As part of my tasks, there is a need to sort a particular table by a certain value, then sort it again, and then sort it again, as in the following example:

SELECT *
  FROM (SELECT * 
       FROM (SELECT * FROM CARS
        ORDER BY BRAND) 
      ORDER BY COLOR)
 ORDER BY YEAR)

While in my opinion, multi-values ORDER BY can be used, like the following:

 SELECT * FROM CARS ORDER BY BRAND, COLOR, YEAR

Note that there is great importance to the runtime of the program in the current task, so even if the code is a little more complicated, but effective and takes less time, it is preferable.

Just for the record, the sort value at each step is created by ROW_NUMBER () OVER (PARTITION BY...
which relevant to each of the values. (e.g. ROW_NUMBER () OVER (PARTITION BY COLOR ORDER BY COLOR) AS C1, and then ORDER BY C1)

So which of the ways is preferable? We should expect to see similar results in both? Or is there another better option?

CodePudding user response：

The code

SELECT *
  FROM (SELECT * 
       FROM (SELECT * FROM CARS
        ORDER BY BRAND) 
      ORDER BY COLOR)
 ORDER BY YEAR)

is synonymous with

SELECT *
FROM CARS
ORDER BY YEAR

If you are seeing that the output looks to be sorted in a cascading fashion, ie, by year, brand, color etc then this is by pure good luck not by anything guaranteed by the SQL engine. The final ORDER BY is the only one that matters (in terms of sorting). [Some times you might see an embedded ORDER BY for reasons of doing pagination, but the final result only depends on the final ORDER BY]

So yes, changing the statement to

SELECT * FROM CARS 
ORDER BY BRAND, COLOR, YEAR

is most probably what you want, because the former is not going the give the result you're after.