SQL: SELECT All the fields from the SECOND BIGGEST ID

Question

My select looks like this and it returns the fields from the biggest id...

SELECT * FROM Pontos WHERE IdPonto = (SELECT MAX(IdPonto) FROM Pontos) 

But now I want to select just the fields from the second biggest.

Answer 1

SELECT * FROM Pontos WHERE IdPonto = (SELECT IdPonto FROM Pontos ORDER BY IdPonto DESC LIMIT 1,1) should work.

Answer 2

This would be one way to do it:

SELECT * FROM Pontos ORDER BY Id DESC LIMIT 1 OFFSET 1

(Not quite certain about sqlite syntax, but read this SO thread for more info: Sqlite LIMIT / OFFSET query)

edit: I do not think it is necessary in this case to run nested queries.

Answer 3

This is written in standard SQL (it doesn't use LIMIT and OFFSET) and should work:

SELECT * FROM Pontos WHERE IdPontos = (
    SELECT MAX(t1.IdPontos) FROM Pontos t1
    WHERE t1.IdPontos not in (SELECT MAX(IdPontos) FROM Pontos)
)

Answer 4

I seems to do the job in standard SQL and in SQLite.

select * from Pontos where IdPonto < (select max(IdPonto) from Pontos) and IdPonto = (select max(IdPonto) from Pontos where IdPonto < (select max(IdPonto) from Pontos));

Hope it helps.

Answer 5

SELECT * FROM Pontos ORDER BY IdPonto DESC LIMIT 1,1