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.

No. Please see. http://stackoverflow.com/questions/1309137/mysql-limit-by-percentage — user3741598, Sep 02 '15 at 20:35
I knew this and thats why I am dividing count() by max(). but this calculation is not working. count() gives me 60028 and max() gives me 409272. but this calculation is not giving me right answer. — Gaurav Gupta, Sep 02 '15 at 20:46
Something doesn't make sense. If you're grouping by User ID, then `COUNT(DISTINCT UserID)` will always be `1`. — Barmar, Sep 02 '15 at 20:47
Here is right query. I made little mistake in above one. 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`)) — Gaurav Gupta, Sep 02 '15 at 20:49
@GauravGupta Don't try to put code in comments, edit the question. Please format it nicely, like I did. Use the `{ }` tool in the SO editor to mark literal code. — Barmar, Sep 02 '15 at 20:52
@Barmar I edited code at the top (in question itself)..kindly help — Gaurav Gupta, Sep 02 '15 at 20:57
Why didn't you use the Code tool in the SO editor? Mark your code, and click on `{}` in the toolbar. Not put `{` and `}` in the question. — Barmar, Sep 02 '15 at 20:58
I can't figure out what you're trying to do. Please update the question and state clearly what you mean by "limit records based on percentage". It looks like you want 10% of something, but I can't figure out what. — Barmar, Sep 02 '15 at 21:02
Post some sample data and the result you're trying to achieve. — Barmar, Sep 02 '15 at 21:02
Edited just now. Trying to figure out about how to post sample data to you. — Gaurav Gupta, Sep 02 '15 at 21:12
@GauravGupta If you want to post a data sample, maybe you can go to [sqlfiddle](http://sqlfiddle.com) and create a working example of what you're trying to do — Barranka, Sep 02 '15 at 21:50
@ Barmar ... Thanks for checking my problem and trying to help me. I got solution---------- set @numerator := (select count(distinct(`User Id`)) from flavia.TableforThe_top_10percent_of_the_user); set @denomenator := (select max(Total_Users_bynow) from flavia.TableforThe_top_10percent_of_the_user); set @totaluser := (round(((@numerator/@denomenator)*0.1)*@numerator)); set @counts:= 0; select *,@counts:=if(@currentuserid like `User Id`, @counts,@counts+1),@currentuserid:=`User Id`from flavia.TableforThe_top_10percent_of_the_user where @counts<=@totaluser; — Gaurav Gupta, Sep 02 '15 at 22:59

score 1 · Accepted Answer · edited May 23 '17 at 11:52

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)

Are you sure limit @limrows works, cause its showing me error here. — Gaurav Gupta, Sep 02 '15 at 22:14

MySQL limit work around

1 Answers1