Find MIN value in a LIMITed set

Question

I need to find a minimum value from limited number of rows in single column. Let's say I have this results table:

-----------------------------------------
|  id  |  category  |  class  |  score  |
-----------------------------------------
|   1  |  contest1  |  seven  |    55   |
|   2  |  contest1  |  sixth  |    78   |
|   3  |  contest2  |  seven  |    20   |
|   4  |  contest1  |  eleven |    21   |
|   5  |  contest2  |  eleven |    56   |
|   6  |  contest3  |  ten    |    66   |
|   7  |  contest3  |  ten    |    90   |
|   8  |  contest3  |  nine   |    91   |
|   9  |  contest2  |  seven  |    30   |
|  10  |  contest1  |  nine   |    51   |
-----------------------------------------

Now I need to create a set of values which are >= 50 AND limited to 3 rows. Like this:

-----------------------------------------
|  id  |  category  |  class  |  score  |
-----------------------------------------
|   1  |  contest1  |  seven  |    55   |
|   2  |  contest1  |  sixth  |    78   |
|  10  |  contest1  |  nine   |    51   |
-----------------------------------------

In this set I have to find MIN value, which is quite simple actually, but only if I do it manually for each category. This query works perfectly:

SELECT MIN(t1.score) 
FROM (
SELECT category, score 
FROM results 
WHERE category = "contest1" AND score >= 50 
ORDER BY score DESC 
LIMIT 3
) t1

And gives the right result:

-------------------
|  MIN(t1.score)  |
-------------------
|        51       |
-------------------

However, I can't write a query which will do it automatically for each category. In a real table there are over a hundred categories with thousands of score values. How can I find minimum value for each category within a limited set of rows?

score 0 · Answer 1 · answered Oct 25 '20 at 12:42

0

Use row_number() to get the "first" three rows for each category:

select r.*
from (select r.*, 
             row_number() over (partition by category order by sid) as seqnum
      from results r
      where score >= 50
     ) r
where seqnum <= 3;

This returns the first three rows based on the id for each category. You can then get the minimum:

select category, min(score)
from (select r.*, 
             row_number() over (partition by category order by sid) as seqnum
      from results r
      where score >= 50
     ) r
where seqnum <= 3
group by category;

answered Oct 25 '20 at 12:42

Gordon Linoff

1,242,037
58
646
786

Thank you for your answer. I'm afraid that's not an option for me cause I have MySQL 5.7. – sauromates Oct 25 '20 at 12:48
@sauromates . . . Why would you want the minimum of three *arbitrary* rows anyway? Why not just use the overall minimum? – Gordon Linoff Oct 25 '20 at 18:32
because overall minimum would be 50 in every category, and I need minimum of 25% top scores in range from 50 to 100. – sauromates Oct 26 '20 at 17:22

nbk · Answer 2 · 2020-10-25T13:44:33.933

You haven't enough rows to make an impact.

But it uses also rwo number to get 3 smallest scores bigger than 50 for every category

CREATE TABLE results  (
  `id` INTEGER,
  `category` VARCHAR(8),
  `class` VARCHAR(6),
  `score` INTEGER
);

INSERT INTO results 
  (`id`, `category`, `class`, `score`)
VALUES
  ('1', 'contest1', 'seven', '55'),
  ('2', 'contest1', 'sixth', '78'),
  ('3', 'contest2', 'seven', '20'),
  ('4', 'contest1', 'eleven', '21'),
  ('5', 'contest2', 'eleven', '56'),
  ('6', 'contest3', 'ten', '66'),
  ('7', 'contest3', 'ten', '90'),
  ('8', 'contest3', 'nine', '91'),
  ('9', 'contest2', 'seven', '30'),
  ('10', 'contest1', 'nine', '51');

SELECT
`id`, `category`, `class`, `score` 
FROM(SELECT 
IF(`category` = @cat,@rown := @rown + 1 ,@rown := 1) rownum
,`id`
, @cat := `category` 'category'
, `class`
, `score` 
FROM (SELECT * FROM results ORDER By `category`, `score` )r1,(SELECT @rown := 0, @cat := '') t1
WHERE `score` > 50 ) t1
WHERE rownum < 4

id | category | class  | score
-: | :------- | :----- | ----:
10 | contest1 | nine   |    51
 1 | contest1 | seven  |    55
 2 | contest1 | sixth  |    78
 5 | contest2 | eleven |    56
 6 | contest3 | ten    |    66
 7 | contest3 | ten    |    90
 8 | contest3 | nine   |    91

db<>fiddle here

Find MIN value in a LIMITed set

2 Answers2