Distribution of table in time

Question

I have a MySQL table with approximately 3000 rows per user. One of the columns is a datetime field, which is mutable, so the rows aren't in chronological order.

I'd like to visualize the time distribution in a chart, so I need a number of individual datapoints. 20 datapoints would be enough.

I could do this:

select timefield from entries where uid = ? order by timefield;

and look at every 150th row.

Or I could do 20 separate queries and use limit 1 and offset.

But there must be a more efficient solution...

Answer 1

Michal Sznajder almost had it, but you can't use column aliases in a WHERE clause in SQL. So you have to wrap it as a derived table. I tried this and it returns 20 rows:

SELECT * FROM (
    SELECT @rownum:=@rownum+1 AS rownum, e.*
    FROM (SELECT @rownum := 0) r, entries e) AS e2
WHERE uid = ? AND rownum % 150 = 0;

Answer 2

Something like this came to my mind

select @rownum:=@rownum+1 rownum, entries.* 
from (select @rownum:=0) r, entries 
where uid = ? and rownum % 150 = 0

I don't have MySQL at my hand but maybe this will help ...