Bug #27709 Subquery and order does not function
Submitted: 9 Apr 2007 9:13 Modified: 9 May 2007 9:34
Reporter: Roberto Taglia Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.37 OS:Windows
Assigned to: CPU Architecture:Any

[9 Apr 2007 9:13] Roberto Taglia
Description:
Here is the SQL statement : 

SELECT *, (select Price from ItemDetail D where D.FKitem = I.KItem order by Price desc limit 1,1) as Price FROM item I ... and so on

ItemDetail contains 3 records with prices 21, 84, 120

With this statement : (select Price from ItemDetail D where D.FKitem = I.KItem order by Price desc limit 1,1)

I wanted to retrieve only the lowest price that is 21, but the MySQL server always returned me the price 84. 

It clearly shows that ORDER BY PRICE ASC does not function.

 

How to repeat:
See above
[9 Apr 2007 9:14] Roberto Taglia
Whatever i put the ascending or descending order, it does not function at all !
[9 Apr 2007 9:34] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW CREATE TABLE for tables ItemDetail and item and complete SELECT statement.
[9 Apr 2007 9:49] Martin Friebe
84 is the expected result for the described issue.

http://bugs.mysql.com/bug.php?id=27709
> With two arguments, the first argument specifies the offset
> of the first row to return, and the second specifies the
> maximum number of rows to return. The offset of the initial
> row is 0 (not 1):
> SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

so that needs to be:
SELECT *, (select Price from ItemDetail D where D.FKitem = I.KItem order by
Price desc limit 0,1) as Price FROM item;

or simply
SELECT *, (select Price from ItemDetail D where D.FKitem = I.KItem order by
Price desc limit 1) as Price FROM item
[9 May 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".