0

How do I find the maximum value of a particular column from the result of a SubQuery?

For example, if the result of the subquery is:

10
20
30

How do I get maximum from this, in this case 30?

closed as off-topic by Kin Shah, Jon Seigel, ypercubeᵀᴹ, Paul White, RolandoMySQLDBA Feb 26 '14 at 18:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jon Seigel, ypercubeᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

1

Example :

If query SELECT id FROM test_table; returns output like

10
20
30

Now use sub query to find Maximum do like

SELECT MAX(a.id) FROM (SELECT id FROM test_table) a;
  • If Strict Mode is On in MySQL, it does not work – user2493976 Feb 26 '14 at 12:04
  • This should work in all modes. What error do you get? – ypercubeᵀᴹ Feb 26 '14 at 12:07
  • @ypercube "Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause" – user2493976 Feb 27 '14 at 4:10
  • Then you are using a different query, not this one. – ypercubeᵀᴹ Feb 27 '14 at 18:38
  • set session sql_mode= 'STRICT_TRANS_TABLES'; select max(id) from (SELECT 0 as id UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) as id; This query returns "5" – Phil Sumner Feb 28 '14 at 14:57
-1

try this:

SELECT * FROM test_table where aa >= all (SELECT aa FROM test_table);
  • 2
    Any explanation for the down votes? This code does work afterall. – Paul Feb 26 '14 at 13:55
  • @Paul I wonder, too. It won't work if there is a NULL in the subquery results but the downvotes might be because there was no explanation. – ypercubeᵀᴹ Feb 26 '14 at 14:07
  • This is actually even better than the MAX solution, for example when there is an index on the queried column in a very basic subselect. In this case the optimizer practically removes all additional computation with this query (at least in decent versions of MySQL), but iterates the rows twice in the other solution. Maybe add an explanation to your answer and an example, with the output of EXPLAIN? – GhostGambler Feb 26 '14 at 14:16
  • 2
    I didn't vote, but this query will return multiple rows if the highest value in the table has duplicates. – Jon Seigel Feb 26 '14 at 14:45
  • 1
    First answers from new users go to a review queue automatically, with limited context. I suspect the down-voters regarded the answer as a low-quality single line of code answer, which is generally discouraged. I'm just guessing though. It is quite a poor question after all. – Paul White Feb 26 '14 at 17:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.