Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is sscm_id the primary key of the table? –  ypercube Nov 2 '12 at 22:14

1 Answer 1

up vote 1 down vote accepted

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 ;
share|improve this answer
    
Works like a charm, thank you! –  user13955 Nov 2 '12 at 22:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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