3

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.

  • Could you post the desired result? – McNets Jan 4 '17 at 8:07
  • How much more than 5 are you supposed to allow? That is, if a complete form is 10_000 rows, do you want to get all of them? [When you say you would get 6 values, that means all 111111 and 222222 ?] – joanolo Jan 4 '17 at 8:09
  • @joanolo Yes exactly, I want "complete" forms, so If my result already contains formID 111111, it needs to increase (or decrease) the limit so it will return all corresponding rows, not just half of the results with formID 111111. It doesn't matter if there are a bit more than 5, cause there will never be 10, a bit less is OK as well ;) – Erik van de Ven Jan 4 '17 at 8:12
  • Obviously some people would say I can just limit on formId, but the results would be much, much more varied. Too much actually. So that's the reason I first want to limit the ID, and then have a check if the results contain "complete" forms. In this last case the difference will be just a few rows. – Erik van de Ven Jan 4 '17 at 8:17
  • @ErikvandeVen: Regarding "some people would say I can just limit on formId, but the results would be much, much more varied" ... I think this issue only exists if you don't repeat the WHERE conditions in your outer query. See my answer for a complete solution. – vog Jan 4 '17 at 9:19
4

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.

  • This assumes that the formID values increase with the created values, which depends heavily on the application and may or may not be true. Depending on that, this solution may or may not work. In general, I find it dangerous to put more assumptions into the solution than given in the question. At least the answer should make these additional assumptions transparent to the reader, up front. – vog Jan 4 '17 at 12:16
  • @vog I did not assume that. I intended to update/improve the answer with a different query if the required order is not BY formID but by something else. If it is by created, then it looks like a gaps-and-islands problem which is much harder on MySQL. – ypercubeᵀᴹ Jan 4 '17 at 15:30
  • I think the MySQL equivalent of limit is SQL Server OFFSET 0 ROWS FETCH FIRST x ROWS ONLY. And, WITH TIES is standardized on OFFSET ... FETCH ... WITH TIES but again, Microsoft decided to only half implement the standardized OFFSET...FETCH syntax and leave WITH TIES on the non-standardized syntax. – Evan Carroll Jan 9 '17 at 19:35
  • @EvanCarroll SQL Server had TOP WITH TIES since version 2000 or earlier.The OFFSET .. FETCH was added in version 2012. They haven't yet implemented the combination of OFFSET .. FETCH .. WITH TIES, true. Thank you, I'm not sure why I didn't add the standard syntax (and only the SQL Server one) in the first paragraph. Edited. – ypercubeᵀᴹ Jan 9 '17 at 19:38
  • 1
    If we bitch about delays, what should we say about mySQL that hasn't yet implemented CTEs? Not to mention that UPDATE is not working as per the standard. – ypercubeᵀᴹ Jan 9 '17 at 19:43
2

The following solution uses a subquery. First, select only the matching formId values, make them unique via GROUP BY, and apply LIMIT to them:

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

Then select all entries with these formId values. Note that you need to repeat the WHERE conditions as well as theORDER BY clause to get a correct result:

SELECT `id`, `formId`, `created`, ...
FROM `forms`
WHERE `formId` IN (
          SELECT `formId`
          FROM `forms`
          WHERE created >= $lastCreated
                AND id != $lastId
          GROUP BY `formId`
          ORDER BY created
          LIMIT 5
      )
      AND created >= $lastCreated
      AND id != $lastId
ORDER BY created

This uses ORDER BY MAX(created) because from your question I assume you want to count the last entries of each formId. Your question was not clear in that regard. Moreover if you just want to limit on any 5 formIds and don't care which one (as long as they are complete), you can remove ORDER BY MAX(created) entirely. (But keep the last ORDER BY clause!)

Hint: The above query may fail with the following error message:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

To work around this MySQL limitation, you can use a special subquery-subquery trick:

SELECT `id`, `formId`, `created`, ...
FROM `forms`
WHERE `formId` IN (
          SELECT * from (
              SELECT `formId`
              FROM `forms`
              WHERE created >= $lastCreated
                    AND id != $lastId
              GROUP BY `formId`
              ORDER BY created
              LIMIT 5
          ) AS temp
      )
      AND created >= $lastCreated
      AND id != $lastId
ORDER BY created

Or, use more feature-complete database such as PostgreSQL.

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.