GKanellis GKanellis - 4 months ago 7
SQL Question

How to get the max of a column in MySQL

I want to get the

id
of a row, based on max number that exists in
number
column on table
x
.

For example:
I have the following records inside
x
table.

id | number
---------+---------
9 | 289
10 | 100
24 | 187
54 | 345


I want a query to return the
id
54 (
SELECT x.id ...
) by finding out that the max of the column
number
is 345
.

How can I succeed that?

Answer

Please try the following query:

Solution #1:

SELECT 
x.*
FROM x
INNER JOIN 
(
  SELECT 
   MAX(number) AS max_number
  FROM x
) AS maxTable
ON x.number = maxTable.max_number

Explanation:

SELECT 
  MAX(number) AS max_number
FROM x;

Result:

maxTable:

max_number
   345

Now make an inner join between your table x and the above table named maxTable on maxTable.max_number and x.number.

x table:

    =====================
    | id      | number  |
    =====================
    | 9       | 289     |
    ---------------------
    | 10      | 100     |
    ---------------------
    | 24      | 187     |
    ---------------------
    | 54      | 345     |
    ---------------------

Thus in the final output you will get only the entries which have maxTable.max_number in their number column.

Solution #2:

You can use WHERE IN to accomplish this too.

SELECT
*
FROM x
WHERE x.number IN ( SELECT MAX(number) FROM x )
Comments