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

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;

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/3682/" >Distribution of table in time< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.