MySQL limit work around

Question

I need to limit records based on percentage but MYSQL does not allow that. I need 10 percent User Id of (count(User Id)/max(Total_Users_bynow) My code is as follows:

select * from flavia.TableforThe_top_10percent_of_the_user where `User Id` in (select distinct(`User Id`) from flavia.TableforThe_top_10percent_of_the_user group by `User Id` having count(distinct(`User Id`)) <= round((count(`User Id`)/max(Total_Users_bynow))*0.1)*count(`User Id`));

Kindly help.

Answer 1

Consider splitting your problem in pieces. You can use user variables to get what you need. Quoting from this question's answers:

You don't have to solve every problem in a single query.

So... let's get this done. I'll not put your full query, but some examples:

-- Step 1. Get the total of the rows of your dataset
set @nrows = (select count(*) from (select ...) as a);
-- --------------------------------------^^^^^^^^^^
-- The full original query (or, if possible a simple version of it) goes here

-- Step 2. Calculate how many rows you want to retreive
-- You may use "round()", "ceiling()" or "floor()", whichever fits your needs
set @limrows = round(@nrows * 0.1);

-- Step 3. Run your query:
select ...
limit @limrows;

---

After checking, I found this post which says that my above approach won't work. There's, however, an alternative:

-- Step 1. Get the total of the rows of your dataset
set @nrows = (select count(*) from (select ...) as a);
-- --------------------------------------^^^^^^^^^^
-- The full original query (or, if possible a simple version of it) goes here

-- Step 2. Calculate how many rows you want to retreive
-- You may use "round()", "ceiling()" or "floor()", whichever fits your needs
set @limrows = round(@nrows * 0.1);

-- Step 3. (UPDATED) Run your query. 
--         You'll need to add a "rownumber" column to make this work.
select *
from (select @rownum := @rownum+1 as rownumber
           , ... -- The rest of your columns
      from (select @rownum := 0) as init
         , ... -- The rest of your FROM definition
      order by ... -- Be sure to order your data
     ) as a
where rownumber <= @limrows

Hope this helps (I think it will work without a quirk this time)