MySQL find rows with max value of a field

Question

I have a complex SELECT query which returns info like this:

+------+-------+--------+
| a    | b     | c      |
+------+-------+--------+
| 2482 |  3681 |  58248 |
| 2482 | 17727 | 139249 |
| 2482 | 23349 | 170839 |
| 3031 | 14877 | 122921 |
| 3031 | 20691 | 156457 |
| 3031 | 20932 | 157784 |
+------+-------+--------+

and I'm trying to work out how to return just the rows with the maximum value of b for each value of a. The solution in here looked promising but I was unable to make it work since my starting point isn't a table but a query (or view, I guess), and I get an error

"ERROR 1146 (42S02): Table 'databasename.s' doesn't exist"

when I try to refer to the subquery in the fashion of the above question:

select s.* from (SELECT blah blah complex) s where s.b = (select max(s2.b) from s s2 where s2.a = s.a);

I'm pretty certain this is a semi-trival problem but I've bashed my head off it for hours and simply don't understand it well enough to navigate my way out of it...

Edit: I should have clarified what I want to get as output:

    +------+-------+--------+
    | a    | b     | c      |
    +------+-------+--------+
    | 2482 | 23349 | 170839 |
    | 3031 | 20932 | 157784 |
    +------+-------+--------+

MySQL version is 5.5.49 (Ver 14.14 Distrib 5.5.49)

UPDATE: @Harshil's answer clarified that what I was attempting was essentially impossible, so I ended up following the guidance in other questions (guided by @Bill Karwin's commment) and rewrote my original query to perform another JOIN (against a subquery) which does the max().

@Neilski, this is a common question, you will find many solutions with the [tag:greatest-n-per-group] tag. — Bill Karwin, Sep 25 '17 at 22:23
Bill - thanks, I will consult those answers more carefully but on a first glance none of them seem to be trying to analyse a query. Seems much easier with an actual table... — Neilski, Sep 25 '17 at 22:29

Harshil Doshi · Answer 1 · 2017-09-25T21:23:06.560

1

The alias s can't be visible in subquery. So you have to write it again in subquery. Also use group by instead of where clause as follows:

select s.* 
  from 
     (SELECT blah blah complex) s 
        where (s.a,s.b) in (select s2.a,max(s2.b) from (SELECT blah blah complex) group by s2.a);

Alternatively, create view of query (SELECT blah blah complex) for i.e. named V and use it in your main query as follows:

select * 
  from V
     where (a,b) in (select v2.a,max(v2.b) from V as v2 group by v2.a);

Hope it helps!

edited Sep 25 '17 at 21:23

answered Sep 25 '17 at 21:10

Harshil Doshi

3,497
3
14
37

Thanks for the help Harshil. I don't know how to create a view (SQL noob), and the first method is too ugly to attempt. I ended up following the guidance in other links and rewrote the original query to perform another JOIN (with subquery) which does the max(). Your help was appreciated, as it made me realise that my subquery would never work (the docs didn't reveal that, so maybe I'm looking at the wrong docs!). @Bill Karwin's reply also guided me to the other questions which helped me to rewrite the query. As a noob on this site I am unsure about where to award credit - guidance welcome! :) – Neilski Sep 26 '17 at 01:26
You can upvote to the answers that leads you towards right direction .If any answer can solve your exact problem, you can accept it as an answer to your question. Both things will be helpful to the new visitors to this post. – Harshil Doshi Sep 26 '17 at 11:58
If you got your solution than it's fine otherwise I can help you to create view. – Harshil Doshi Sep 26 '17 at 11:59
OK thanks Harshil. I've upvoted your answer although my noob status means it isn't showing the vote (!). No answer solved my exact problem though so I'll update my original question to note how I worked around it... – Neilski Sep 27 '17 at 01:18
Yes! Upvote is not visible because you don't have enough rep(15). Thanks for the mention in your question though. If you got your exact solution then I'll suggest that you should add the solution with the use of 'Answer your own question' feature.And if you don't have enough rep for that too, you can just add the actual query/solution in your question. Thank you again. Welcome to SO! – Harshil Doshi Sep 27 '17 at 14:10

ivan.rosina · Answer 2 · 2017-09-25T20:56:36.443

0

 select a,b,c from examp where (a,b) in (select a ,max(b) from examp group by a );

edited Sep 25 '17 at 20:56

answered Sep 25 '17 at 20:51

ivan.rosina

368
3
9

Thanks. The original version you posted looked promising and basically worked on my sample data but I think it may not work on non-monotonic data. The new version above fails with the same "Table 'databasename.s' doesn't exist" error as my own botched attempts. – Neilski Sep 25 '17 at 21:06
Sorry I don't understand. I filled my examp table with your values and the result match what you asked. The problem you're reporting is related to something else. Post your complete query: the problem is there – ivan.rosina Sep 25 '17 at 21:35
it's because the query result isn't a table I believe. – Neilski Sep 25 '17 at 22:25

score -1 · Answer 3 · answered Sep 25 '17 at 21:07

Firstly, use

SELECT * INTO newTableName 

FROM yourRules

to save the query to a new table.

Then we can do another select from the new table.

SELECT *
FROM newTableName t1
WHERE b = (
     SELECT max(b) 
     FROM newTableName t2
     WHERE t1.a = t2.a
)

MySQL find rows with max value of a field

3 Answers3