0

I have the following SQL statement by now:

SELECT m . *
FROM newsletter_mail_list m
INNER JOIN (
    SELECT n.id
    FROM newsletter n
    GROUP BY n.customer_id
    ORDER BY n.id ASC
) b ON m.newsletter_id = b.id
WHERE m.sent_date IS NULL

Mail List Table

+--------+-------+--------+--------+-----+
| id | newsletter_id | email | sent_date |
+--------+-------+--------+--------+-----+
| 1  | 8             | abcd  | today     |
| 2  | 8             | cfdf  | NULL      |
| 3  | 8             | afdg  | NULL      |
| 4  | 9             | zfbh  | NULL      |
| 5  | 9             | eerg  | NULL      |
| 6  | 9             | ertg  | NULL      |
| 7  | 9             | zfbh  | NULL      |
| 8  | 9             | eerg  | NULL      |
| 9  | 9             | ertg  | NULL      |
| 10 | 9             | zfbh  | NULL      |
| 11 | 9             | eerg  | NULL      |
| 12 | 9             | ertg  | NULL      |
| 13 | 9             | zfbh  | NULL      |
| 14 | 9             | eerg  | NULL      |
| 15 | 9             | ertg  | NULL      |
| 16 | 9             | zfbh  | NULL      |
| 17 | 9             | eerg  | NULL      |
| 18 | 9             | ertg  | NULL      |
+--------+-------+--------+--------+-----+

Desired Result

+--------+-------+--------+--------+-----+
| id | newsletter_id | email | sent_date |
+--------+-------+--------+--------+-----+
| 2  | 8             | cfdf  | NULL      |
| 3  | 8             | afdg  | NULL      |
| 4  | 9             | zfbh  | NULL      |
| 5  | 9             | eerg  | NULL      |
| 6  | 9             | ertg  | NULL      |
| 7  | 9             | zfbh  | NULL      |
| 8  | 9             | eerg  | NULL      |
| 9  | 9             | ertg  | NULL      |
| 10 | 9             | zfbh  | NULL      |
| 11 | 9             | eerg  | NULL      |
| 12 | 9             | ertg  | NULL      |
| 13 | 9             | zfbh  | NULL      |
+--------+-------+--------+--------+-----+

This one gives me all mail_list rows, where the sent_date is NULL and the foreign newsletter is the oldest from the customer.

Now I only want maximal 10 mail_list rows returned FOR EACH newsletter returned by the subselect.

Example:

By now I'm getting 21 results by this SQL, 3 mail_lists that belong to the newsletter 8 and 18 results for the newsletter 9. But the number of results for the newsletter 9 should only be 10.

How could I do this with SQL? Is this even possible?

KhorneHoly
  • 4,666
  • 6
  • 43
  • 75
  • It's great to see that you've attempted something, but even better... consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Mar 17 '16 at 14:22
  • 1
    Thank you @Strawberry, I've edited my question – KhorneHoly Mar 17 '16 at 14:30

1 Answers1

1

E.g.: 

SELECT x.*
  FROM mail_list x 
  JOIN mail_list y 
    ON y.newsletter_id = x.newsletter_id 
   AND y.id <= x.id 
 WHERE x.sent_date IS NULL 
 GROUP 
    BY newsletter_id
     , id 
HAVING COUNT(*) <= 10;

If performance is an issue, then a solution with variables will be faster...

E.g.:

SELECT id
     , newsletter_id
     , email
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev=newsletter_id 
                   THEN @i:=@i+1 
                   ELSE @i:=1 END i
            , @prev:=newsletter_id prev 
         FROM mail_list x
            , (SELECT @prev:='',@i:=0) vars 
        WHERE sent_date IS NULL
        ORDER 
           BY newsletter_id, id
     ) a
 WHERE i <= 10;
Strawberry
  • 33,750
  • 13
  • 40
  • 57