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.
Subject
Written By
Posted
subquery bug ?
March 10, 2017 02:01AM
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.