SQL row with max value

In this post we will analyze how to retrieve the row with max value on a given column, working on a SQL database.

Suppose we have a table with some data inside it:

Id	Name	Value
1	Blue	2.80
2	Red	3.90
3	Green	1.20
4	Black	4.30
5	White	2.10

We will call this table `Colors`. We want to build a query that returns the row 4. The SQL code to construct the table:

CREATE TABLE `Colors` (
 `Id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `Name` VARCHAR(255),
 `Value` DECIMAL(15, 2)
) AUTO_INCREMENT = 1;

INSERT INTO `Colors` (`Id`, `Name`, `Value`) VALUES (1, "Blue", 2.8);
INSERT INTO `Colors` (`Id`, `Name`, `Value`) VALUES (2, "Red", 3.9);
INSERT INTO `Colors` (`Id`, `Name`, `Value`) VALUES (3, "Green", 1.2);
INSERT INTO `Colors` (`Id`, `Name`, `Value`) VALUES (4, "Black", 4.3);
INSERT INTO `Colors` (`Id`, `Name`, `Value`) VALUES (5, "White", 2.1);

Method1 

Subquery as a scalar operand.

SELECT *
 FROM `Colors`
 WHERE `Value` = (
  SELECT MAX(`Value`)
  FROM `Colors`
  WHERE 1
 )

Method2

Join with no matches.

SELECT `x`.*
FROM `Colors` AS `x`
LEFT JOIN `Colors` AS `y` ON `y`.`Value` > `x`.`Value`
WHERE `y`.`Id` IS NULL

Method3

MySql group by tricky. (This works only in MySql, and we use the fact that MySql returns the first row in a group by statement when no aggregation function is applied on a given column).

SELECT `Id`, `Name`, `Value`
FROM (
 SELECT `Id`, `Name`, `Value`, 1 AS `X`
 FROM `Colors`
 ORDER BY `Value` DESC 
) AS `x`
GROUP BY `X`