Professional Web Applications Themes

SELECTing a fixed number of entries per day - MySQL

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 ...

  1. #1

    Default 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.
    Bruno Guest

  2. #2

    Default Re: SELECTing a fixed number of entries per day

    On Jan 30, 12:26 pm, Bruno Barberi Gnecco
    <sourceforge.net> wrote: 

    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;

     

    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 Guest

  3. #3

    Default Re: SELECTing a fixed number of entries per day

    com wrote: 
    >
    >
    > 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;[/ref]

    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.
     
    >
    >
    > 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;[/ref]

    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.
    Bruno Guest

  4. #4

    Default Re: SELECTing a fixed number of entries per day

    On Feb 4, 10:38 am, Bruno Barberi Gnecco
    <sourceforge.net> wrote: 

    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...
     

    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 Guest

  5. #5

    Default Re: SELECTing a fixed number of entries per day

    com wrote: 
    >
    >
    > 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...[/ref]

    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.
     
    >
    >
    > 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...[/ref]

    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/weighted-randomness-using-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
    Bruno Guest

Similar Threads

  1. FORM CALCULATIONS: multiplying 2 fields with a fixed number
    By mary_rios@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 14
    Last Post: February 2nd, 10:57 PM
  2. counting number of unique entries under a column
    By mr_burns in forum PHP Development
    Replies: 2
    Last Post: October 9th, 07:29 PM
  3. Substition for fixed number of characters
    By Al Roy in forum PERL Miscellaneous
    Replies: 4
    Last Post: August 30th, 05:46 PM
  4. Replies: 5
    Last Post: August 15th, 12:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139