Questions tagged [mysql-error-1242]

When a subquery returns more than one row in a context where one row is expected, you get the following error:

Error 1242: Subquery returns more than 1 row

Consider for example the following query that returns the row in yourtable with the maximum associated id:

SELECT yourtable.*
FROM yourtable
WHERE id = (SELECT MAX(id) FROM yourtable)

This works fine because the subquery returns no more than one row.

You might then want to obtain all rows with the maximum id for every name, so you have to add a GROUP BY clause in the subquery, but you also have to modify the outer query this way:

SELECT yourtable.*
FROM yourtable
WHERE id IN (SELECT MAX(id)
             FROM yourtable
             GROUP BY name)

(here an IN clause is needed because the subquery could return more than one row).

Or you can always use a LIMIT 1, like in this example that returns the row with the maximum id for the greatest name:

SELECT *
FROM yourtable
WHERE id = (SELECT MAX(id)
            FROM yourtable
            GROUP BY name
            ORDER BY name DESC
            LIMIT 1)