MySQL – Using LIMIT with Complete Sets

MySQLsubquery

I guess this is a pretty tough one. I want to get results from the database, per 5 rows max (so a limit of 5 each time), but each time I don't want to return incomplete "sets" either. Look at my table of forms:

+----+--------+------------+
| id | formID |  created   |
+----+--------+------------+
|  1 | 111111 | 2014-01-01 |
+----+--------+------------+
|  2 | 111111 | 2014-01-01 |
+----+--------+------------+
|  3 | 111111 | 2014-01-01 |
+----+--------+------------+
|  4 | 222222 | 2014-01-01 |
+----+--------+------------+
|  5 | 222222 | 2014-01-01 |
+----+--------+------------+
|  6 | 222222 | 2014-01-01 |
+----+--------+------------+
|  7 | 333333 | 2014-01-02 |
+----+--------+------------+

So I want to return my results in sets of 5 database rows BUT with complete forms. In this case I will have incomplete results, cause I don't have all rows of form with ID 222222 returned. So either I need to get 3 results (less than 5 but complete forms) or 6 (a bit more than 5 but also complete forms).

Anyone who has any idea how to accomplish this?

At the moment my query looks, more or less, like this:

SELECT `id`, `formId`, `created`
FROM `forms`
WHERE created >= $lastCreated
      AND id != $lastId
ORDER BY created
LIMIT 5;

(this is just an example so no worries about the sql injection vulnerability ;)). As you can see I check for the last returned row id and created column every time I run a new query, so I know where I ended. But like I said, this will return a complete form 111111 but an incomplete form 222222.

Best Answer

What you want is called TOP WITH TIES in SQL Server and FETCH FIRST .. WITH TIES in standard SQL. The MySQL LIMIT clause is equivalent to TOP (and FETCH FIRST) but it doesn't have WITH TIES functionality.

Here is one way to do this. The details will differ, depending on what you want the ORDER BY to be based on. I used formID:

SELECT f.*
FROM forms AS f
  INNER JOIN
    ( SELECT formID
      FROM forms
      ORDER BY formID
      LIMIT 1 OFFSET 4        -- offset 5 minus 1
    ) AS fm
  ON  f.formID <= fm.formID 
ORDER BY f.formID ;

This will return 6 rows in your example case. If you replace <= with < it will return 3 rows.

In order to try and adapt that to your specific case more closely, you can add the created column to the "equation":

SELECT f.*
FROM forms AS f
  INNER JOIN
    ( SELECT created, formID
      FROM forms
      WHERE created >= '2014-01-01'
      ORDER BY created, formID
      LIMIT 1 OFFSET 4
    ) AS fm
  ON  (f.created, f.formID) <= (fm.created, fm.formID)
WHERE f.created >= '2014-01-01' ;

This Rextester demo shows how it works both with <= and with <.

I am not sure about the id != $lastId predicate in your query, but if it is an actual part of the filter rather than just your attempt at resolving the issue in question, then you will probably need to add it to the above query as well, both to the main WHERE and to the nested WHERE.

Related Question