SQL–how to find nth row / nth order element

Example below is self-explanatory – you should assign rank by desirable criteria and then just group by your categories (product id and name is sample below), selecting only entries of certain rank. In sample below you would select all entries with second biggest ID per category.

CREATE TABLE  #test ( id int, ProductName VARCHAR(25) )
insert into #test
select 1, 'Apple' union all
select 2, 'Apple' union all
select 5, 'Apple' union all
select 3, 'Orange' union all
select 4, 'Orange' union all
select 10, 'Orange'

SELECT * FROM #test

SELECT maxID FROM
(
    SELECT MAX(id) AS maxID, ProductName AS nn, RANK() OVER (PARTITION BY ProductName ORDER BY id DESC) AS MyRank
    FROM #test
    GROUP BY id, ProductName
) tmp
WHERE tmp.MyRank = 2