MySQL Forums
Forum List  »  General

subquery bug ?
Posted by: Mike ART
Date: March 10, 2017 02:01AM

Hi folks, I gotta a question about using mysql subquery.

say I have a table TEST like this:

+------+------+-----------+
| id | type | name |
+------+------+-----------+
| 1 | 5 | beef 1 |
| 3 | 44 | beef 2 |
| 7 | 51 | unknown |
| 1 | 2 | unknown2 |
| 1 | 9 | missing |
| 3 | 18 | what? |
| 7 | 7 | ok |
| 7 | 88 | well done |
| 3 | 101 | puff |
+------+------+-----------+


I expect a result like this (pick the biggest from each id group):

+------+------+---------+
| id | type | name |
+------+------+---------+
| 1 | 9 | beef 1 |
| 3 | 101 | beef 2 |
| 7 | 88 | unknown |
+------+------+---------+

so I do a subquery like this:

select * from (select * from test order by id,type) as t
group by id;

+------+------+---------+
| id | type | name |
+------+------+---------+
| 1 | 5 | beef 1 |
| 3 | 44 | beef 2 |
| 7 | 51 | unknown |
+------+------+---------+

isn't the result strange? the subquiry is not working at all, it simply picks up one from each type which appears first in the origianl table.

I'm using mysql 5.7.9.

thanks.

Options: ReplyQuote


Subject
Written By
Posted
subquery bug ?
March 10, 2017 02:01AM
March 10, 2017 12:21PM
March 11, 2017 09:52PM
March 11, 2017 02:27PM
March 11, 2017 09:42PM
March 11, 2017 10:02PM
March 13, 2017 10:50PM
March 16, 2017 10:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.