MySql: How to limit how many rows are printed

Question

Here is my table sampletable4 (Just a sample). My table is millions of data.:

      app_id    latency    -- app_id is char(255), latency is float
    | 1111   |    0.940056 |
    | 1110   |    0.261509 |
    | 3211   |   0.0506242 |
    | 3210   |    0.468594 |
    | 3110   |    0.191095 |

Where app_id is a string and latency is float. The latency measures how fast an API call for each app.

I would like to print out the slowest (or highest latency) for each app_id. My query does not limit to slowest 5%. I want to limit the query to slowest 5% of each app_id.

Here is my query:

  SELECT 
  latency  
  FROM sampleTable4
  WHERE app_id = '5697'
  ORDER BY latency DESC;

It is a specific case for app_id = '5697'. How would I change it so it print out 5% of the latency?

meet · Answer 1 · 2017-01-05T11:10:11.083

1

SELECT latency FROM  (SELECT latency,(SELECT FLOOR(COUNT(*)*0.05) AS per_count   
  FROM sampleTable4
  WHERE app_id = '5697'  ) AS 5per_count,IF(@cnt=NULL,@cnt:=1,@cnt:=@cnt+1) AS cnt 
  FROM sampleTable4 ,(SELECT @cnt:=0) AS T
  WHERE app_id = '5697'
  ORDER BY latency DESC ) FT       
  WHERE cnt<=5per_count

edited Jan 05 '17 at 11:10

answered Jan 05 '17 at 09:52

meet

166
11

No. I want one query. – Jan 05 '17 at 09:53
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements). – meet Jan 05 '17 at 10:04
Is there a way to get .05 without limit? – Jan 05 '17 at 10:06
Could you simplify it? – Jan 05 '17 at 11:09
What does @ mean? – Jan 05 '17 at 11:09

MySql: How to limit how many rows are printed

1 Answers1