CODEWITHSUNDEEP
sqlsqlitegreatest-n-per-group
phate river
phate river

Reputation: 47

How to get the 2nd highest number without using LIMIT and OFFSET?

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?

Upvotes: 1

Views: 419

Answers (1)

jpw
jpw

Reputation: 44911

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?

Upvotes: 2

Related Questions