Taking the second last row with only one select in SQL Server?

The best way to do this (and compatible with the ANSI SQL standard), is to use a CTE (Common Table Expression) with the ROW_NUMBER function:

;WITH OrderedCinemas AS
(
   SELECT 
       CinemaID, CinemaName, 
       ROW_NUMBER() OVER(ORDER BY CinemaID DESC) AS 'RowNum'
   FROM dbo.Cinema
)
SELECT 
   CinemaID, CinemaName
FROM OrderedCinemas
WHERE RowNum = 2

By using this construction, you can get the second highest value very easily - or the fifth hightest (WHERE RowNum = 5) or the top 3 rows (WHERE RowNum <= 3) or whatever you need - the CinemaID values are just ordered and sequentially numbered for your use.

To get the 2nd last row in one select:

SELECT TOP 1 * From
(select Top 2 * from Cinema ORDER BY CinemaID DESC) x                     
ORDER BY CinemaID

It's really only "one" select because the outer select is over only 2 rows.

The following doesn't work, explaination of why: Using ranking-function derived column in where clause (SQL Server 2008)

I'll keep it here for completeness:

---

SELECT row_number() OVER (ORDER BY col) r, *
FROM tbl
WHERE r = 2

More info: http://www.bidn.com/blogs/marcoadf/bidn-blog/379/ranking-functions-row_number-vs-rank-vs-dense_rank-vs-ntile

---

So I think the most readable way of doing it is:

SELECT * FROM (SELECT row_number() OVER (ORDER BY col) r, * FROM tbl) q
WHERE r = 2