# Limiting query to certain amount of rows evenly distributed

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.

• @rbhalla different to linked answer - in your case it seems first and last entry are "fixed", so you actually need to only locate n-2 rows in between, it is easy to add first to those and make it n-1, but you will probably never get last item with dynamicaly computed step with rounding, so be explicit about that one and simplify the rest. Commented Jun 15, 2015 at 20:18
• @user1786423 You are correct, I only really need to calculate n-2. I am not hazy on the logic required here. My question was regard the most efficient way to get these values where I don't need to parse an entire table (as described in that question I linked). Commented Jun 18, 2015 at 9:26
• @rbhalla thats why I am not answering and only commenting - can't see a way - maybe using index-only scan instead of table scan, but that's still all rows, only less data Commented Jun 18, 2015 at 11:04

You have a dynamic number of rows in the table, and want a specific number of evenly distributed results each time?

``````   SELECT *
FROM (
SELECT
@row := @row +1 AS rownum, [column name]
FROM (
SELECT @row :=0) r, [table name]
) ranked
WHERE rownum % [n] = 1
``````

Just replace `[n]` with the following:

``````(CEIL(@row/[m]))
``````

where `[m]` is the number of results you want to have returned.