0

If I have following table:

CREATE TABLE `docs` ( 
    `id` int(6) unsigned NOT NULL, 
    `rev` int(3) unsigned NOT NULL, 
    `content` varchar(200) NOT NULL, 
--
    PRIMARY KEY (`id`) 
)

and execute following query: 

select * 
from ( 
    select * 
    from docs 
    order by rev desc 
) as `rows`

will the order of returned rows be the same as order of inner query?

Can this be guaranteed, generally speaking?

Yuriy Nakonechnyy
  • 3,742
  • 4
  • 29
  • 41
  • Yes, the order is the same – juergen d Oct 10 '14 at 14:36
  • @juergend thanks for answer, but can this be confirmed somehow by docs or anything? This seemed obvious to me as well, but comment to my answer intrigued me: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column/24556626?noredirect=1#answer-24556626 :) – Yuriy Nakonechnyy Oct 10 '14 at 14:42
  • I'm sure that int 3 and int 6 don't do whatever it is that you think they do! And I'm not sure that @juergen's right... But then again, he usually is! – Strawberry Oct 10 '14 at 15:34
  • For anyone answering this, remember that an inner ORDER BY inside a UNION may be optimized away! Yes, I know this is different, but still. Also I think the OP is asking for a guarantee as in "per the SQL spec". I doubt there is such a guarantee tbh. – Jannes Oct 11 '14 at 19:11
  • http://dev.mysql.com/doc/refman/5.0/en/union.html says "If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway." I haven't seen such a statement for the query in question here, but I don't see why it couldn't do that. – Jannes Oct 11 '14 at 19:11

1 Answers1

2

yes, If you are only using 

select * 
from ( 
    select * 
    from docs 
    order by rev desc 
) as `rows`

then it will be same as always But using ORDER BY in subquery should not be done. Subquery used in some outer query, and that outer query will have to do ordering anyway, so there's no point ordering the subquery

if you use TOP or LIMIT in the subquery, you will need to use ORDER in Subquery. But that's not standard SQL

You should use it this way

SELECT * 
FROM ( 
    SELECT * 
    FROM docs 
) AS `rows` ORDER BY rev DESC;
Lalit Bhudiya
  • 4,332
  • 4
  • 26
  • 32
  • The reason why it should be done is to solve `select rows with max-value` problem like in the following answer: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column/24556626?noredirect=1#answer-24556626 and hence the question – Yuriy Nakonechnyy Oct 10 '14 at 15:01
  • @yura none of suggestions provided under the accepted answer at that thread rely on this 'technique' – Strawberry Oct 10 '14 at 15:39
  • @Strawberry yes, that's why I added that answer :) – Yuriy Nakonechnyy Oct 13 '14 at 08:44