Using LIMIT in a subquery based on another field in MySQL

Question

Is it possible to use LIMIT based on another column inside a subquery in MySQL? Here is a working query of what I mean.

SELECT id, name,
 (SELECT AVG(value) FROM t2 WHERE t1id = t1.id ORDER BY value DESC LIMIT 4) as average 
  FROM t1

However I'd like to replace the "4" to a field inside t1.

Something like this where table t1 has fields id, name, size:

SELECT id, name,
 (SELECT AVG(value) FROM t2 WHERE t1id = t1.id ORDER BY value DESC LIMIT t1.size) as average 
  FROM t1

I could join t1 and t2, but I'm not sure that works for this. Does it?

Edit: Here's some sample data to show what I mean:

Table t1

| id | name | Size |
|----|------|------|
| 1  | Bob  | 4    |
| 2  | Joe  | 3    |
| 3  | Sam  | 4    |

Table t2

| t1id | value |
|------|-------|
| 1    | 16    |
| 1    | 14    |
| 1    | 12    |
| 1    | 10    |
| 1    | 8     |
| 2    | 10    |
| 2    | 8     |
| 2    | 6     |
| 2    | 4     |
| 3    | 20    |
| 3    | 15    |
| 3    | 10    |
| 3    | 5     |
| 3    | 2     |

Expected result:

| id | name | avg  |
|----|------|------|
| 1  | Bob  | 13   |
| 2  | Joe  | 8    |
| 3  | Sam  | 12.5 |

Notice that the average is the average of only the top t1.size values. For example the average for Bob is 13 and not 12 (based on 4 values and not 5) and the average for Joe is 8 and not 7 (based on 3 values and not 4).

Please provide sample data and desired results.

Gordon Linoff
– Gordon Linoff

2020-12-28 18:48:48 +00:00
Commented Dec 28, 2020 at 18:48 — Gordon Linoff
– Gordon Linoff, Commented Dec 28, 2020 at 18:48
I included sample data to show what I'm looking for.

GKarRacer
– GKarRacer

2020-12-28 20:12:15 +00:00
Commented Dec 28, 2020 at 20:12 — GKarRacer
– GKarRacer, Commented Dec 28, 2020 at 20:12

Gordon Linoff · Accepted Answer · 2020-12-28 20:23:15Z

3

In MySQL, you have little choice other than LEFT JOIN and aggregation:

SELECT t1.id, t1.name, AVG(t2.value) as average
FROM t1 LEFT JOIN
     (SELECT t2.*,
             ROW_NUMBER() OVER (PARTITION BY t1id ORDER BY VALUE desc) as seqnum
      FROM t2
     ) t2
     on t2.t1id = t1.id AND seqnum <= t1.size
GROUP BY t1.id, t1.name;

Here is a db<>fiddle.

edited Dec 28, 2020 at 20:23

answered Dec 28, 2020 at 18:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

GKarRacer Over a year ago

This looks promising. However, either I'm doing it wrong or it's not working. I'm getting the full average and not the limited average with this query. See added example data for clarification.

Gordon Linoff Over a year ago

@GKarRacer . . . On the data you have in your question, it returns the specified results. Hence, I don't understand your comment.

GKarRacer Over a year ago

Nevermind. I did have something wrong when I converted this up to the larger query. It does work. Excellent.

Bill Karwin · Accepted Answer · 2020-12-28 18:52:46Z

0

No, you cannot use a column reference in a LIMIT clause.

https://dev.mysql.com/doc/refman/8.0/en/select.html has detailed documentation about MySQL's SELECT statement including all its clauses.

It says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

Expressions, including subqueries, are not mentioned as legal argument in the LIMIT clause.

A simple solution would be to do your task in two queries: the first to get the size and then use that value as a constant value in the second query that includes the LIMIT.

Not every task needs to be done in a single SQL statement.

answered Dec 28, 2020 at 18:52

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

2 Comments

GKarRacer Over a year ago

I have no problem with 2 queries. The problem here is that it is not constant. The limit may be different for each row in t1.

Bill Karwin Over a year ago

Okay, point taken. But it remains true that you can't use a column reference in a LIMIT clause.

Collectives™ on Stack Overflow

Using LIMIT in a subquery based on another field in MySQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related