There is a mysql query

SELECT `temp`.* FROM (SELECT `price`.`Code`,`price`.`Name`,`price`.`Warranty`,`price`.`Price` FROM `price` ORDER BY `price`.`Price`) AS temp

The request does not sort by price . Price Those. gives out not sorted data. At the same time request

 SELECT `price`.`Code`,`price`.`Name`,`price`.`Warranty`,`price`.`Price` FROM `price` ORDER BY `price`.`Price`

works fine. Tell me what could be the problem?

I introduce an amendment to the question:

In fact, my query looks like this:

  SELECT `temp`.* FROM (SELECT `price`.`Code`,`price`.`Name`,`price`.`Warranty`,`price`.`Price`,(@rownum:=@rownum+1)as num FROM (SELECT @rownum:=0) AS initialisation,`price` ORDER BY `price`.`Price` LIMIT 41) AS temp WHERE `num`>=21 and `num`<41

I need to sort the goods by price and take 20 consecutive elements from the sorted ones. In some cases, the option with LIMIT works, in some it is not. I can not understand how in this case to do.

  • four
    There are no problems. Everything works in accordance with the SQL standards, which say that order by may affect the order of the final sample only if it is applied at the end of the query, i.e. to the very final sample - Mike
  • @Mike transferred to the general answer. If you want to post an answer on your behalf - let me know. - Kromster
  • @Kromster Yes, no, and already found a less lazy person who painted in more detail :) - Mike

2 answers 2

It should be

In the documentation of many DBMS this is described, for example, from MariaDB :

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery clause) do not come in any specific order. That's why you can specify. In fact, it doesn’t make it possible to quantify the order.

There is no need for any further information.

That is, the DBMS by default treats the tables and the FROM results as an unordered rowset, and only at the end of the ORDER BY query is required to work.

It should be noted that in different DBMS (and even versions of the same DBMS) this behavior may differ. For example, in MySQL 5.1 (and perhaps a bit newer), sorting works within subqueries. Also, as seen in the quote above, in MariaDB, sorting in subqueries works, if there is a LIMIT . At the same time, in MS SQL, the presence of ORDER BY outside of an external query is generally considered an error.

Based on this answer from enSO

  • In MS SQL, the presence of ORDER BY in a subquery is allowed if there is a TOP (local analogue of LIMIT ). But at the same time, the sorting of the result is not guaranteed - Herman Borisov

Everything works in accordance with the SQL standards, which say that order by may affect the order of the final sample only if it is applied at the end of the query, i.e. to the final sample itself.