SQL: Way to select 2nd largest ID?

Question

I have a SQL table with two fields that matter for this select:

ID - unique numeric ID
NAME

I want to select the row having the 2nd largest ID where the NAME equals a known value. Is there a way to do this with a SQL statement or do I need to select all rows with NAME = to my value and parse out the row I want in a loop?

I know I can order the results by ID and just loop to the 2nd result, but I'd rather have the answer returned to me with a single SQL call.

Answer 1

SELECT MAX(ID) FROM table_name WHERE ID IN (SELECT ID FROM table_name MINUS SELECT MAX(ID) FROM table_name)

Answer 2

You can use the MAX keyword to retrieve the largest item in a row so your sql can be as follows:



SELECT MAX(ID) FROM TABLENAME WHERE NAME='value'

Answer 3

select name, max(ID) from MyTable where ID <> (select max(ID) from MyTable) and name = 'string'

GO