MySql: #1221 – Incorrect usage of UNION and ORDER BY

MySQLorder-bysyntaxunion

I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts.

SELECT * FROM comment AS tbl WHERE sscm_id IN 
(
  SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
    ORDER BY t1.sscm_id DESC LIMIT 3
  UNION  ALL
  SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
    ORDER BY t2.sscm_id DESC LIMIT 3
)

which gives me the following error:

1221 – Incorrect usage of UNION and ORDER BY

Some searching & googling seemed to suggest that placing () around each subquery might solve this problem but changing the query to 

SELECT * FROM comment AS tbl WHERE sscm_id IN
(
  (SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
     ORDER BY t1.sscm_id DESC LIMIT 3)
  UNION  ALL
  (SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
     ORDER BY t2.sscm_id DESC LIMIT 3)
)

but this gives me the following error:

1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL (SELECT sscm_id FROM bpsocial_comment AS t2 WHERE t2.sscm_oid = 3 OR' at line 4

So: how can I get the UNION in combination with the ORDER BY in the subqueries to work?

Thanks
R

Best Answer

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM     
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp 
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.* 
FROM comment AS tbl 
    JOIN 
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION 
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp 
      ON tmp.sscm_id = tbl.sscm_id ;
Related Question