MySQL - using one value from each group using some conditions

Question

I have a table like this in MySQL. I want to calculate average of values (column Value) but not using all rows. From each group (Column Group) I want to use only one value, with the biggest rank (Column Rank) if there are multiple rows in that group.

+----+-------+-------+------+
| ID | Value | Group | Rank |
+----+-------+-------+------+
|  1 |    10 |     1 |    1 |
+----+-------+-------+------+
|  2 |     9 |     1 |    2 |
+----+-------+-------+------+
|  3 |     7 |     2 |    2 |
+----+-------+-------+------+
|  4 |    10 |     2 |    1 |
+----+-------+-------+------+
|  5 |    11 |     3 |    1 |
+----+-------+-------+------+
|  6 |     9 |     4 |    1 |
+----+-------+-------+------+
|  7 |     8 |     5 |    1 |
+----+-------+-------+------+
|  8 |    10 |     6 |    2 |
+----+-------+-------+------+
|  9 |     9 |     7 |    1 |
+----+-------+-------+------+

So, in group 1 I must use value 9 from row ID 2 because it has the biggest rank. In group 2 I will use value 7 from row ID 3 because it has the biggest rank. And the rest I will use the only values because there are no alternatives. In the end I want to calculate the average of values from rows 2, 3, 5, 6, 7, 8, 9. How can I do that in one query?

is (group,rank) unique? if not, what value is used if there are multiple ranks for the same group? — ysth
– ysth, Commented Jul 29, 2021 at 22:59

ysth · Accepted Answer · 2021-07-29 23:00:19Z

1

You can use NOT EXISTS to filter only the rows with the highest rank from each group:

SELECT AVG(t1.Value) avg_value
FROM tablename t1
WHERE NOT EXISTS (
  SELECT 1
  FROM tablename t2
  WHERE t2.group = t1.group AND t2.rank > t1.rank
)

Or, with a correlated subquery:

SELECT AVG(t1.Value) avg_value
FROM tablename t1
WHERE t1.rank = (SELECT MAX(t2.rank) FROM tablename t2 WHERE t2.group = t1.group)

See the demo.

edited Jul 29, 2021 at 23:00

ysth

99.1k6 gold badges126 silver badges220 bronze badges

answered Jul 29, 2021 at 22:49

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ysth Over a year ago

in mysql 8 or mariadb 10.2+, you can use FIRST_VALUE for this: dbfiddle.uk/…

forpas Over a year ago

@ysth yes but you still need a subquery.

ysth Over a year ago

not all subqueries are created equal :) I would bet window functions outperform both of your queries where there are large numbers of records with a lot of ranks for each group

forpas Over a year ago

@ysth don't bet on that. Window functions need a full table scan for each partition. EXISTS does not need that. It returns as soon as it finds a match. Also, not all window functions are as fast as you believe. For example row_number() is (usually) faster than rank() and max() is (usually) faster than first_value().

ysth Over a year ago

often the most efficient way to do this kind of thing is

SELECT AVG(Value) FROM (SELECT CAST(SUBSTR(MAX(CONCAT(LPAD(t.Rank,10,0),t.Value)),11) AS INTEGER) AS Value FROM tablename t GROUP BY t.Group) AS max_rank_values

(assuming unsigned ranks) but that's kind of gross

Collectives™ on Stack Overflow

MySQL - using one value from each group using some conditions

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related