1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Rep Power

    How to get source rowcount into Limited Aggregate?

    I have a pretty complex query that gets all rows under 10 seconds old and then aggregates the top 10 rows for some field after throwing away the best record using a LIMIT 1,10.
    The query works fine but I would like to know how many rows were returned before being 'limited'.
    If I just use count() then the answer will always be 10.

    Here is a working sample query where the Max Size = 4 ( top record thrown away) the avg = 3.25 and the count = 4 ( from the limit ) - what I need is to return a count of 6

    Any ideas how to include the source rowcount without running another query?


    create temporary table tempstuff( size int, lastUpdated date);

    insert into tempstuff values(1, '2001-1-1');
    insert into tempstuff values(2, '2001-1-2');
    insert into tempstuff values(3, '2001-1-1');
    insert into tempstuff values(4, '2001-1-1');
    insert into tempstuff values(4, '2001-1-1');
    insert into tempstuff values(5, '2001-1-1');

    select Max(size),avg(size), Count(size) from
    (select * from tempstuff
    where lastupdated > '2000-1-1'
    order by size desc
    limit 1,4) as A ;

    drop table tempstuff;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    forgo the aggregates, use SELECT SQL_CALC_FOUND_ROWS in the LIMIT query, then issue SELECT FOUND_ROWS()... and then do the aggs on those 10 returned rows in php or whatever your application language is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo