SELECTing a fixed number of entries per day

This is a simple question, but I haven't figured a way to solve it... I have
a table with a DATE column, and I want to make a SELECT that returns N random entries
from this table for each day in a given month. The result I want looks like this:

date some data
----------------------------------
2007/01/01 Entry 16
2007/01/01 Entry 32
2007/01/01 Entry 13
2007/01/02 Entry 2
2007/01/02 Entry 35
2007/01/02 Entry 54
....

Is there a faster way to do this than running 30 SELECTs?

And how to give different weights to each entry for this select?

Thanks a lot!

--
Bruno Barberi Gnecco <brunobg_at_users.sourceforge.net>
There must be at least 500,000,000 rats in the United
States; of course, I never heard the story before.

On Jan 30, 12:26 pm, Bruno Barberi Gnecco
<brunobgDELETET...@users.sourceforge.net> wrote:
> This is a simple question, but I haven't figured a way to solve it... I have
> a table with a DATE column, and I want to make a SELECT that returns N random entries
> from this table for each day in a given month. The result I want looks like this:
>
> date some data
> ----------------------------------
> 2007/01/01 Entry 16
> 2007/01/01 Entry 32
> 2007/01/01 Entry 13
> 2007/01/02 Entry 2
> 2007/01/02 Entry 35
> 2007/01/02 Entry 54
> ...
>
> Is there a faster way to do this than running 30 SELECTs?

I got this off the MySQL Select Syntax web manual. It was from one of
the comments at the end, and it works perfectly for me. Just change
the limit to however many random entries you want. So, if you want 30
random entries, change to LIMIT 30;

If your tables are not all that big, a simpler method is:
SELECT * FROM my_table ORDER BY RAND(NOW()) LIMIT 1;


> And how to give different weights to each entry for this select?

If you add a weight system to your database, you can just add a where
clause in your select statement and only select those that are above a
certain weight... Just one suggestion. It would be kinda like this:

SELECT * FROM my_table WHERE weight_parameter > 3 ORDER BY RAND(NOW())
LIMIT 1;

Hope that helps,
Nino

nino9stars@yahoo.com wrote:
> On Jan 30, 12:26 pm, Bruno Barberi Gnecco
> <brunobgDELETET...@users.sourceforge.net> wrote:
>
>> This is a simple question, but I haven't figured a way to solve it... I have
>>a table with a DATE column, and I want to make a SELECT that returns N random entries
>>from this table for each day in a given month. The result I want looks like this:
>>
>>date some data
>>----------------------------------
>>2007/01/01 Entry 16
>>2007/01/01 Entry 32
>>2007/01/01 Entry 13
>>2007/01/02 Entry 2
>>2007/01/02 Entry 35
>>2007/01/02 Entry 54
>>...
>>
>> Is there a faster way to do this than running 30 SELECTs?
>
>
> I got this off the MySQL Select Syntax web manual. It was from one of
> the comments at the end, and it works perfectly for me. Just change
> the limit to however many random entries you want. So, if you want 30
> random entries, change to LIMIT 30;
>
> If your tables are not all that big, a simpler method is:
> SELECT * FROM my_table ORDER BY RAND(NOW()) LIMIT 1;

Two problems with this: first, I don't need 30*N out of the month,
but N of each day. That's what I don't know how to do with just one select.

Second, ORDER BY RAND() reorders the entire table, which is
considerably slow... See http://jan.kneschke.de/projects/mysql/order-by-rand/
But this solution only returns one random entry, I can't see how to make
it work for more than one.

>> And how to give different weights to each entry for this select?
>
>
> If you add a weight system to your database, you can just add a where
> clause in your select statement and only select those that are above a
> certain weight... Just one suggestion. It would be kinda like this:
>
> SELECT * FROM my_table WHERE weight_parameter > 3 ORDER BY RAND(NOW())
> LIMIT 1;

Actually, what I need is to weight the probability to select
an entry: an entry with weight 2 would be twice as likely to be
randomly selected than one with weight 1.

Any other ideas? Thanks for your answer,

--
Bruno Barberi Gnecco <brunobg_at_users.sourceforge.net>
Herth's Law:
He who turns the other cheek too far gets it in the neck.

On Feb 4, 10:38 am, Bruno Barberi Gnecco
<brunobgDELETET...@users.sourceforge.net> wrote:
> Two problems with this: first, I don't need 30*N out of the month,
> but N of each day. That's what I don't know how to do with just one select.

Sorry... Completely misread this. I had actually written about this
ages ago and never recieved a reply. Seemed the only way I could do it
was to write it within my code in JSP. However, I was just snooping
around the MySQL site and noticed they finally added subqueries! I
have been so busy, I really haven't had a chance to play with it yet,
but I imagine it might be a solution to both of our problems. Here is
the link to the manual: http://dev.mysql.com/doc/refman/5.1/en/
subqueries.html

After all, in my code, the answer was simply to loop the result within
the next query. Perhaps some simple manipulation of a subquery might
do the trick? I wish I had more time to really sit down and help
figure it out... if I stumble on anything I will definitely let you
know though...

> Actually, what I need is to weight the probability to select
> an entry: an entry with weight 2 would be twice as likely to be
> randomly selected than one with weight 1.

Not sure that subqueries will help with this though... That's a tricky
one... You could always duplicate the entry, and then technically your
odds improve on selecting it randomly... but I realize that is pretty
lame, and not very efficient...

Nino

nino9stars@yahoo.com wrote:
> On Feb 4, 10:38 am, Bruno Barberi Gnecco
> <brunobgDELETET...@users.sourceforge.net> wrote:
>
>> Two problems with this: first, I don't need 30*N out of the month,
>>but N of each day. That's what I don't know how to do with just one select.
>
>
> Sorry... Completely misread this. I had actually written about this
> ages ago and never recieved a reply. Seemed the only way I could do it
> was to write it within my code in JSP. However, I was just snooping
> around the MySQL site and noticed they finally added subqueries! I
> have been so busy, I really haven't had a chance to play with it yet,
> but I imagine it might be a solution to both of our problems. Here is
> the link to the manual: http://dev.mysql.com/doc/refman/5.1/en/
> subqueries.html
>
> After all, in my code, the answer was simply to loop the result within
> the next query. Perhaps some simple manipulation of a subquery might
> do the trick? I wish I had more time to really sit down and help
> figure it out... if I stumble on anything I will definitely let you
> know though...

I can't figure out how to solve it with subqueries, but I
could figure it out with stored procedures. I'd like to hear your
idea.

This is the pseudocode of my query:

CREATE PROCEDURE monthselect()
BEGIN
SET @i = 1;

CREATE TEMPORARY TABLE rangedata LIKE originaltable;
CREATE TEMPORARY TABLE datalist LIKE originaltable;
INSERT INTO rangedata SELECT * FROM originaltable WHERE /* IN RANGE */;
REPEAT
INSERT INTO datalist SELECT * FROM rangedata WHERE DAY(starts)= @i+FIRST_DAY ORDER BY
your_metric LIMIT 5;
SET @i = @i + 1;
UNTIL @i < total_days_in_range
END REPEAT;
END

The rangedata TABLE is created to improve performance, but you
could get it from the originaltable as well. This works pretty well
for me, and I think it will scale well enough for me. The number of
results I get is small enough to use RAND(). Solved most of my
problems.

>> Actually, what I need is to weight the probability to select
>>an entry: an entry with weight 2 would be twice as likely to be
>>randomly selected than one with weight 1.
>
>
> Not sure that subqueries will help with this though... That's a tricky
> one... You could always duplicate the entry, and then technically your
> odds improve on selecting it randomly... but I realize that is pretty
> lame, and not very efficient...

Yeah, not a very good idea... This is a good idea, but it may
be tricky to use in dynamic data:

http://www.zulugrid.com/2006/06/20/w...php-and-mysql/

Again, any ideas are welcome.

--
Bruno Barberi Gnecco <brunobg_at_users.sourceforge.net>
That's the difference between me and the rest of the world! Happiness isn't
good enough for me! I demand euphoria! -- Calvin