mysql subquery where and group by

Question

I'm learning mysql manual: http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html and i'm confused with the "where.s1.article = s2.article' part, what is this mean?

Task: For each article, find the dealer or dealers with the most expensive price.

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+


SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

I'm trying to write the similar code:

SELECT sex, name, birthday
From   user
Where  birthday=(SELECT MAX(birthday)
              FROM user
              group by sex);

But mine outputs the error: subquery return more than 1 row, why?

Answer 1

The original query is called a correlated subquery. The WHERE clause in the subquery restricts the maximum found to just the article that matches the row being processed by the outer query.

In your query, you don't have a WHERE clause, so it processes everything in the table. It then returns results grouped by sex. I assume there are two sexes in your table, so it returns one row with the maximum for men, and another row containing the maximum for women. But WHERE price = (<subquery>) requires the subquery to return just a single result, because = can only be used with single values.

You can use IN instead of = to match against multiple returned values. This probably isn't what you want, but you haven't described what you want your query to do so it's hard to tell.

To get the youngest person of each sex, you can write a correlated subquery, like the one in the manual:

select sex, name, birthday
from user u1
where birthday = (select max(birthday)
                  from user u2
                  where u1.sex = u2.sex)

or you can use a join with a grouped subquery:

select u1.sex, name, u1.birthday
from user u1
join (select sex, max(birthday) maxbirthday
      from user
      group by sex) u2
on u1.sex = u2.sex and birthday = maxbirthday