06.14.06

Desired percentage of rows?

Posted in MySQL at 10:41 pm by gloomy

How to make MySQL return a desired percentage of rows?

Some people time after time ask this question. Most of
them are migrating from MsSQL (Microsoft SQL Server 2xxx) that
has the ability to retrieve a desired percentage of rows. For example:

SELECT TOP 50 PERCENT * FROM table

This query will return 50% of rows. Nice. What do you do
in MySQL if you get a task “to return half of the records from a table”?
So far i found just one easy solution for MySQL 5.0.7+ version:

SELECT @percentage := ROUND(COUNT(*) * 50/100) FROM table;
PREPARE STMT FROM 'SELECT * FROM table LIMIT ?';
EXECUTE STMT USING @percentage;

Any thoughts?

9 Comments »

  1. Incognito-R said,

    July 24, 2006 at 10:34 am

    It seems very interesting. I’ve never encountered this problem, but I think that it will be very useful to me someday :-)

  2. Incognito-R said,

    July 24, 2006 at 10:36 am

    By the way, who are you :-) ?. Write something more informational in the page About me :-)

  3. gloomy said,

    July 24, 2006 at 10:42 am

    Hi,

    You mean you want more info because maybe you know me?

    I dont think so ;)

    And what comes to percentage of rows, during the time from the post i have found some better solution:

    SELECT @total := COUNT(*), @counter := 0 FROM table;
SELECT table.*,
@counter := @counter + 1 AS counter
FROM table
HAVING @counter < @total * 50 / 100;

    And this should even work on MySQL 3.23 i guess ;)

  4. Incognito-R said,

    July 24, 2006 at 8:03 pm

    well, it would be really nice to now a man, who’s reading my weblog :-) . How did you find it, and why do you read it? is it interesting to you?

  5. gloomy said,

    July 24, 2006 at 8:17 pm

    As a lithuanian blog - yes, i’ve found at least 2 posts quite interesting ;) (about the JS evens and mambo CMS usage). And to tell the truth - i don’t remembet where did i find your blog - i guess on someone’s else blogroll ;)

  6. Incognito-R said,

    July 24, 2006 at 11:05 pm

    I’m delighted :-)

  7. Carpii said,

    December 3, 2006 at 5:26 am

    Hmm, looks good enough. I wonder what performance is like.
    Count(*) isnt too good on mysql still :-(

  8. j.monty said,

    February 5, 2007 at 6:55 am

    What would be the code to, in another seperate query, make sure to get the remaining half of the records back?

  9. gloomy said,

    February 5, 2007 at 10:22 am

    Carpii: i think COUNT(*) is good enought, the variables should slow the query down;

    j.monty: the rest of the records you get with:

    ... HAVING @counter > @total * 50 / 100;

Leave a Comment