2

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.

3
  • @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.
    – jkavalik
    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).
    – rbhalla
    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
    – jkavalik
    Commented Jun 18, 2015 at 11:04

1 Answer 1

-1

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:

   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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.