SELECT statement that uses order by on a column not being selected-CodePudding

For example when i try this i get no sorting:

SELECT A,B
FROM Table
ORDER BY C

But i only want to display column A,B in the output

I am using MySQL

CodePudding user response：

it can be achieved by adding Sub Query in 'from' clause. You can do something like below

select 
t.A,
t.B
from 
(SELECT A,B,C FROM Table ORDER BY C) t;

Don't go with this solution it wrong one , i was not aware that is ORDER BY limited to the outermost "SELECT".

thanks @jariha and @Erwin for clarification in comments.

CodePudding user response：

This should be possible, sanctioned by what the current version of the SQL standard calls "extended sort keys".

However :

It constitutes a departure from SQL's original intent, whereby there was a "conceptual order of execution" in which the ORDER BY was executed after the SELECT, meaning anything mentioned in the ORDER BY had to be contained in the SELECT.

It constitutes yet one more departure from Codd's foundational "information principle", because the result now "carries meaning" that is only expressed by the ordering of the rows as returned by the DBMS. For this reason, it might arguably be the case that SQL cannot even legitimately be seen as implementing a bag algebra, when what it should really have been was set algebra. Don't think it won't ever bite you ...

Although, being part of the core spec of the standard and not one of the (many) "optional features", it should be the case that all engines support this feature correctly, you might run into one that doesn't because it still goes by the elder rule. Or that supports this feature only if you install some optional module or change some configuration option or so.

Although the case you mention should work properly, there are quite a bit of cases where it isn't (and cannot be) allowed. Decrypting the rules in this respect is, as usual, not a very trivial exercise. For example, if you later want to add a LIMIT or TOP n clause or some such, it seems highly likely that this change will move the query into "ORDER BY C not allowed" territory.

For these reasons, it is not terribly unwise to follow "principle of cautious design" and just stick with the old rules.

So just do SELECT A,B,C FROM ... and ignore the C column in whatever processing you do on the client side.