Mysql Finding the smallest n from a column

Question

So in MySQL you can find the smallest n using sub queries or limit or even TOP in some cases. But I wanted to know if it was possible to find the smallest N without using any of these? I was told this was possible but have no idea where to start. I assume I have to inner join multiple times but beyond that, I am clueless.

For reference, suppose I have a column called salary with the following data (10,20,30,40). If n=2, that means I need to find the second smallest salary, which would be 20.

Answer 1

E.g.:

DROP TABLE IF EXISTS salary;

CREATE TABLE salary (i INT NOT NULL PRIMARY KEY);

INSERT INTO salary VALUES (10),(20),(30),(40);


SELECT SUBSTRING_INDEX(
          SUBSTRING_INDEX(
             GROUP_CONCAT(i ORDER BY i)
          ,',',2)
       ,',',-1)n 
  FROM salary;
+------+
| n    |
+------+
| 20   |
+------+

or (specifically for where n=2)

SELECT MIN(i) x 
  FROM salary 
  LEFT 
  JOIN (SELECT MIN(i) y FROM salary) j 
    ON y = i 
 WHERE y IS NULL;
+------+
| x    |
+------+
|   20 |
+------+