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

Paul Spiegel · Accepted Answer · 2019-09-16T17:52:49.297

2

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.

edited Sep 16 '19 at 17:52

answered Sep 16 '19 at 17:47

Paul Spiegel

30,925
5
44
53

Yes, it seems to work perfectly. You even correctly added ORDER BY which I forgot :-) – Henrik Hansen Sep 16 '19 at 17:55
Not **yet** :-) See [10.4-fiddle](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=c5d2b58ed9dd6e303075d2ad0c23a50e). You can try [Postgres](https://dbfiddle.uk/?rdbms=postgres_8.4&fiddle=9aba173645b7096bd5a6a6e3aece231e) – Paul Spiegel Sep 16 '19 at 19:16

score 2 · Answer 2 · answered Sep 16 '19 at 17:58

2

WITH can be used in MySQL8.0 (,or MariaDB 10.2.1 )

WITH abc as (SELECT DISTINCT T_DOCUMENT FROM VBS_DOCUMENT LIMIT 2 OFFSET 2)
SELECT * FROM VBS_DOCUMENT WHERE T_DOCUMENT IN (select * from abc);

output:

# pk, t_document
'7', '5'
'8', '5'
'9', '6'

answered Sep 16 '19 at 17:58

Luuk

12,245
5
22
33

Is there a version of MariaDB supporting my own trial with IN SELECT LIMIT ? – Henrik Hansen Sep 16 '19 at 18:59
AFAIK there is no way to use `LIMIT` in a subquery, alternative is to use `JOIN`, see: https://stackoverflow.com/questions/12810346/alternative-to-using-limit-keyword-in-a-subquery-in-mysql. A JOIN *can* be seen as an alternative to the WITH variant... – Luuk Sep 17 '19 at 17:14

Select by a limited number of distinct values

2 Answers2