So I was creating a table with some minions which look like this:
CREATE TABLE Minion(MinionID varchar(10), gold int, Atk int, def int);
with the values:
INSERT INTO Minion VALUES ('min001', 15, 5, 20);
INSERT INTO Minion VALUES ('min002', 20, 7, 30);
INSERT INTO Minion VALUES ('min003', 18, 8, 17);
INSERT INTO Minion VALUES ('min004', 30, 15, 100);
INSERT INTO Minion VALUES ('min005', 5, 3, 9);
INSERT INTO Minion VALUES ('min006', 60, 23, 150);
INSERT INTO Minion VALUES ('min007', 10, 8, 20);
INSERT INTO Minion VALUES ('min008', 65, 40, 250);
INSERT INTO Minion VALUES ('min009', 300, 75, 550);
INSERT INTO Minion VALUES ('min010', 300, 65, 600);
INSERT INTO Minion VALUES ('min011', 300, 80, 400);
So I was wondering, without LIMIT and OFFSET, is it still possible to get the 2nd highest e.g. atk of the minions?
To get the second highest value you can apply max to a set that excludes the max value:
select max(atk) from Minion where atk <> (select max(atk) from Minion)
With your sample data this would return atk = 75.
But why not use the functions like limit and offset when they are available?