Select Top X (Or Bottom) Percent for Numeric Values in MySQL

Select TOP X (or bottom) percent for numeric values in MySQL

UPDATE: Much more thought-out explanation of the subject from much more knowing person here. Nonetheless, it still seems there's no embedded function in MySQL to calculate percentiles.

Try:

~~SELECT * FROM prices WHERE price >= (SELECT 0.9 * max(price) FROM prices)~~

SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

This will give price P1 for which number of records in Price table having price >= P1 will be one tenth of total number of records in Price table.
After that:

SELECT * FROM prices WHERE price >= (SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

will return all desired records.

Note: I didn't examine performance of this query, I think solution with temporary table/variable must be more effective.

MySQL select top 10% of users

Hope this will help you

I used CURRENT_DATE to limit search.

SELECT user_id, date, total_time FROM
(
  SELECT user_id, @rownum:=@rownum+1 AS rownum 
  FROM TABLE_USER , (SELECT @rownum:=0) R
  WHERE date = CURRENT_DATE 
  ORDER by total_time desc
) temp 
where rownum < (select count(*) from TABLE_USER where date = CURRENT_DATE) / 10

SELECT TOP PERCENT, VaR, Expected Shortfall in MySQL

The reason why the end result was not according to the single value queries was caused by the @row_number assignment. Taking the base query (the subquery) to run alone will return the following results:

PROFIT	counter	log
-100	1	101
-90	2	101
-80	3	101
-70	4	101
-60	5	101
-50	6	101
-5	7	100
-4	8	100
-3	9	100
-2	10	100
-1	11	100
500	12	101

Limit by percentage of all rows in MySQL

you can use this code:

SELECT*
FROM    (
    SELECT list.*, @counter := @counter +1 AS counter
    FROM (select @counter:=0) AS initvar, list
    ORDER BY value DESC   
) AS X
where counter <= (10/100 * @counter);
ORDER BY value DESC

quote from this duplicate question

MySQL - Limit by bottom (X) percent of rows

This assumes that you want the average of the cheapest 15% for each item.

The following query enumerates the rows for each item and gets the total rows:

  select d.*, cnt,
         if(@item = item, @rn := @rn + 1, if(@item := item, 1, 1)) as rn
  from `data` d left join
       (select item, count(*) cnt
        from data
        group by item
       ) di
       on d.item = di.item cross join
       (select @rn := 0, @item := -1) vars
  order by item, price/quantity;

You can then basically plug this into your query and do conditional aggregation:

SELECT item AS id,
       COUNT(item) as total,
       ROUND(AVG(price/quantity)) AS mean,
       ROUND(MIN(price/quantity)) AS cheapest,
       avg(case when rn <= cnt * 0.15 then price/quantity end) as Cheapest15Percent
FROM (select d.*, cnt,
             if(@item = item, @rn := @rn + 1, if(@item := item, 1, 1)) as rn
      from `data` d left join
           (select item, count(*) cnt
            from data
            group by item
           ) di
           on d.item = di.item cross join
           (select @rn := 0, @item := -1) vars
      order by item, price/quantity
     ) d
GROUP BY item;

get top and bottom 25th percentile average

This is a solution that uses a devious trick I learned from this question.

SELECT id, unit_sold, n * 100 / @total AS percentile
FROM (
  SELECT id, unit_sold, @total := @total + unit_sold AS n
  FROM mydata, (SELECT @total := 0) AS total
  ORDER BY unit_sold ASC
) AS t

SQL Fiddle.

MySQL: LIMIT by a percentage of the amount of records?

Best answer I found:

SELECT*
FROM    (
    SELECT list.*, @counter := @counter +1 AS counter
    FROM (select @counter:=0) AS initvar, list
    ORDER BY value DESC   
) AS X
where counter <= (10/100 * @counter);
ORDER BY value DESC

Change the 10 to get a different percentage.

How do I select TOP 5 PERCENT from each group?

You could use a CTE (Common Table Expression) paired with the NTILE windowing function - this will slice up your data into as many slices as you need, e.g. in your case, into 20 slices (each 5%).

;WITH SlicedData AS
(
   SELECT Category, Name, COUNT(Name) Total,
            NTILE(20) OVER(PARTITION BY Category ORDER BY COUNT(Name) DESC) AS  'NTile'
   FROM #TEMP
   GROUP BY Category, Name
)
SELECT *
FROM SlicedData
WHERE NTile > 1

This basically groups your data by Category,Name, orders by something else (not sure if COUNT(Name) is really the thing you want here), and then slices it up into 20 pieces, each representing 5% of your data partition. The slice with NTile = 1 is the top 5% slice - just ignore that when selecting from the CTE.

See:

MSDN docs on NTILE
SQL Server 2005 ranking functions
SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

for more info

Select Top X (Or Bottom) Percent for Numeric Values in MySQL