Select by a limited number of distinct values

Question

I am trying to chunkage selects by distinct values of one column. Like give me all rows for the first five distinct values. Or give me all rows for next five distinct values of one column.

I have a table like this VBS_DOCUMENT:

PK    T_DOCUMENT
1     1
2     1
3     1
4     3
5     3
6     3
7     5
8     5
9     6
10    7

SELECT * FROM VBT_DOCUMENT 
WHERE T_DOCUMENT IN (SELECT DISTINCT T_DOCUMENT FROM VBT_DOCUMENT LIMIT 2 OFFSET 2);

But then I get this error:

1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

PK    T_DOCUMENT
7     5
8     5
9     6

Answer 1

Use your subquery in the FROM clause and join it with the base table:

SELECT t.*
FROM (
  SELECT DISTINCT T_DOCUMENT 
  FROM VBT_DOCUMENT
  ORDER BY T_DOCUMENT
  LIMIT 2 OFFSET 2
) x
JOIN VBT_DOCUMENT t on t.T_DOCUMENT = x.T_DOCUMENT

Result:

| PK  | T_DOCUMENT |
| --- | ---------- |
| 7   | 5          |
| 8   | 5          |
| 9   | 6          |

View on DB Fiddle

Note: When you use LIMIT you should also define a deterministic ORDER BY clause. Otherwise you might get an unexpected result depending on the execution plan.