GKanellis GKanellis - 4 months ago 6
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?

Thanks in advance.

Answer

Please try the following query:

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.