SQL: Help me selecting record in a 1 -> N -> N relation

Question

I've Client Client can have N Delivery Each Delivery can have N Sms

Delivery has client_id Sms as Delivery_id

I'd like to retrieve all sms from Sms table, but max 10 sms for each client.

This is because 'sms' is a queue of sms to send. So i'd like to keep bilanced initially in this simple way the distribution of sms sent from every client.

Is there a way to accomplish this using only SQL ?

If I join client to delivery to sms ... how to select only ten sms foreach client ?

I'm USING MYSQL with InnoDb

As far as I know there is no "SQL" way, but almost every DBMS has a way if you can limit yourself to a specific one. — Joachim Isaksson, Jan 03 '13 at 09:24

score 1 · Accepted Answer · edited May 23 '17 at 11:43

This is not easy to do in mysql. This SO question has some potentially useful answers:

ROW_NUMBER() in MySQL

In particular, the article http://www.explodybits.com/2011/11/mysql-row-number/ should help.

In case it helps others, for SQL Server (2005 or up) it can be done thus:

WITH
ClientSms AS
(
  SELECT
    Client.Client_id,
    Sms.Content,
    ROW_NUMBER() OVER
    (
      PARTITION BY Client.Client_id
      ORDER BY Sms.Content
    ) AS RowNumber
  FROM Client
    INNER JOIN Delivery ON Delivery.Client_id = Client.Client_id
    INNER JOIN Sms ON Sms.Delivery_id = Delivery.Delivery_id
)
SELECT
    Client.Client_id,
    Sms.Content
FROM ClientSms
WHERE RowNumber <= 10
ORDER BY
    Client.Client_id,
    Sms.Content

ederbf · Answer 2 · 2013-01-03T10:20:44.557

Edit: I tried again, this time using MySQL and with a Fiddle to test it. The code is very dirty and i think that you can do this without some of the SELECTs, but it's the way I cam come up with to fake a row count. Hope it is helpful.

SELECT * 
FROM (
    SELECT
        (SELECT COUNT(*)
         FROM (
           SELECT s.smsid, c.clientid
           FROM sms s INNER JOIN delivery d ON d.deliveryid = s.deliveryid
                      INNER JOIN client c ON c.clientid = d.clientid
         ) AS numberedSMS
         WHERE numberedSMS.smsid <= numberedSMS2.smsid 
               AND
               numberedSMS.clientid = numberedSMS2.clientid) AS rowNum,
        smsid AS smsid
    FROM (
           SELECT smsid, c.clientid
           FROM sms s INNER JOIN delivery d ON d.deliveryid = s.deliveryid
                      INNER JOIN client c ON c.clientid = d.clientid
         ) numberedSMS2
    ORDER BY numberedSMS2.smsid asc 
      ) as r
WHERE r.ROWNUM <= 10

Here you have the SQLFiddle code

this select only 10 sms. I need to select AT MOST 10 sms for each client — realtebo, Jan 03 '13 at 09:28

SQL: Help me selecting record in a 1 -> N -> N relation

2 Answers2