Given a table:
id
--
1
2
4
6
8
10
I would like to be able to specify a limit and have that amount of rows returned evenly distributed across the entire set when it is ordered by a specific criteria. For example, 3 rows when the set is ordered by id would give me:
id
--
1
4
10
In the above scenario, either 4
or 6
could have been returned as the middle row. I am not specifically bothered as to which way the selection is rounded.
Ideally I'd like to do this in the most efficient way i.e. the entire table isn't parsed initially.
I found an excellent post here Select every n-th row, don't pull the entire table, regarding evenly distributed data sets but struggled when trying to calculate n in order to limit the final data set to a specific amount.
I'm currently running MySQL 5.6 on Ubuntu.
-
Ids are non sequential.
-
This database is dynamic so I can't hardcode the amount of rows or have select statements that reflect errors in a particular set of results.
Best Answer
You have a dynamic number of rows in the table, and want a specific number of evenly distributed results each time?
I'm asking because the solution you linked is already nearly there:
Just replace
[n]
with the following:where
[m]
is the number of results you want to have returned.