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`

ROT13 Cryptography

ROT13 is one of the most simple cryptography systems, is a good way to give an introduction  of how the cryptography works!
A cryptography system is based on:

• The plain text: P
• An invertible function: F
• The inverse of F: I
• The encoded text: E = F(P)
• The plain text again: P = I(F(P))

Here is a javascript implementation of ROT13

function F(P) {
    var x, E = "";

    for (var i = 0; i < P.length; i++) {
        x = P.charCodeAt(i);
        if (x >= 65 && x <= 90) //'A' = 65 | 'Z' = 90
            x = (x - 65 + 13) % 26 + 65;
        if (x >= 97 && x <= 122) //'a' = 97 | 'z' = 122
            x = (x - 97 + 13) % 26 + 97;
        E = E + String.fromCharCode(x);
    }

    return E;
}

As you can observe:

F ≡ I (ROT13 is his own inverse).

This can be explained:

x + 13 ≡ x - 13 (mod 26) for any given x