2

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

  • Is sscm_id the primary key of the table? – ypercubeᵀᴹ Nov 2 '12 at 22:14
1

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 ;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.