Does TSQL have a 2nd instead of TOP 1 to return one record

[Rx_]

Is there a TSQL Statement like TOP 1 that would return the 2nd value?

Considering avoiding cursors.
The Test returns the TOP 1 of a sorted recordset.
About 99.85% of the time, this satisfies the Scalar Function.
For the 0.15% of the time, the next record in that sorted recordset needs to be evaluated.

Example: formula with a If - Then statement could be used.
If this record is < rule > then return value - and done
Else if Recordset Count > 1 then look to see if 2nd record is <rule > return value - and done

Found this example, it would allow an Ascending for first part of IF then the DESC for the Else. Would this be worth coding or can someone think of another solution?

The 2nd line selects the top 2 rows and by using 'ORDER BY ROW_COUNT DESC", the 2nd row is arranged as being first, then it is selected using TOP 1

SELECT TOP 1 COLUMN1, COLUMN2 from (
SELECT TOP 2 COLUMN1, COLUMN2 FROM Table) ORDER BY ROW_NUMBER DESC

 

[CJ_London]

I would probably come to the same suggestion but another one might be (not tested and no idea if more efficient)

 SELECT A.*
 FROM (select top 2 Indx, ColA, ColB from table) as A 
     left join (select top 1 Indx from table) as B
         on A.indx=B.indx
     WHERE B.indx is null

 

[CSL]

Would Offset Fetch do the trick? But you'd still have to evaluate when it was offset by how much.

 

[MarkK]

Wouldn't this work?

SELECT TOP 1 * 
FROM (
    SELECT TOP 2 * 
    FROM Table1
    ORDER BY Field1
    )
ORDER BY Field1 DESC

 

[Rx_]

Updated: 4/20/2015
Thanks.
After releasing the data, the Regulatory team was able to see throught the chaff. So, they had a meeting and re-designed the business rules.
I have been working Sundays to catch up.

Also did some more research. Your suggestion not only appeared to be the better solution, someone on some post went into details of how it works best with the execution plan.

I am probably still two days out from trying it. Will be sure to follow up with the results.

Saving this until last, finishing up six other cases before then.
Thanks! All good thoughts. Will report back with my results.