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

Question

I was trying to select the second last row with SQL Server. So I wrote a query like this:

SELECT TOP 1 * From Cinema 
WHERE CinemaID!=(SELECT TOP 1 CinemaID 
                 FROM Cinema
                 ORDER BY CinemaID DESC)                      
ORDER BY CinemaID DESC 

and it did what I need. But I want to do the same thing with only one select.

I read that the LIMIT clause in MySql does that. But I couldn't find any equivalent of that. So I appreciate any help about finding something useful.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

Since this (old) question has not been tagged with a specific SQL-Server version and none of (the very good) answers uses only one SELECT clause - for the good reason that it was not possible in old verions - here is one that works only in recent, 2012+ versions:

SELECT c.* 
FROM dbo.Cinema AS c                    
ORDER BY CinemaID DESC
OFFSET 1 ROW
FETCH FIRST 1 ROW ONLY ; 

Tested at SQLFiddle

Answer 5

SELECT TOP 1 * FROM tbl_CompanyMaster 
where Companyid >= (SELECT MAX(Companyid) - 1 FROM tbl_CompanyMaster)

Answer 6

select * from TABLE_NAME order by COLUMN_NAME desc limit 1,1 ;

Where COLUMN_NAME should be "primary key" or "Unique"

Answer 7

This query will also work for SQLITE

SELECT *  From
(select * from Cinema ORDER BY CinemaID  DESC LIMIT 2) AS name                    
ORDER BY CinemaID LIMIT 1

Answer 8

Two selects but a bit quicker

select top 1 * from(
SELECT TOP 2 * From Cinema 
WHERE CinemaID
ORDER BY CinemaID DESC) top2
Order by CinemaID

Answer 9

So, in the spirit of only using one SELECT clause as stated in the OP and thoroughly abusing T-SQL in general, I proffer something I would never, ever recommend using in production that nevertheless satisfies the stated criteria:

update Cinema
set Cinema.SomeField = Cinema.SomeField
output inserted.*
from Cinema
inner join 
(
    select top 2 CinemaID, ROW_NUMBER() over (order by CinemaID desc) as RowNum
    from Cinema
) rsRowNum on rsRowNum.CinemaID = Cinema.CinemaID
where RowNum = 2

Answer 10

SELECT field_name FROM (SELECT TOP 2 field_name FROM table_name 
                        ORDER BY field_name DESC)
WHERE rownum = 2;

Answer 11

select top 1* from(SELECT TOP 2 * From Cinema 
                   WHERE CinemaID
                   ORDER BY CinemaID DESC) XYZ
ORDER BY CinemaID

where XYZ is not a keyword. It is just a word. And word can be anything.

Answer 12

If you need to do that, but:

• the column is different than id
• you need to order by some specific column
• can't use SELECT on FROM clause (if you are using old versions of Hibernate, per example).

You can do:

select top 1 * from Cinema
where date < (select MAX(date) from Cinema)
order by date desc

Answer 13

The easiest way to get second last row from sql table is user ORDER BY CinemaID DESC and set LIMIT 1,1

TRY THIS

SELECT * from `Cinema` ORDER BY `CinemaID` DESC LIMIT 1,1

Answer 14

select * from 
(select  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as R, * from Cinema ) T1
where (select count(*) from Cinema ) - T1.R = 1

Answer 15

SELECT * FROM record WHERE record_id=(SELECT max(record_id)-1 FROM record)

Answer 16

You're only using one SELECT statement. A SELECT statement can include an arbitrary (more or less) number of subqueries--correlated subqueries, scalar subqueries, and so on, each with their own SELECT clause. But it's still just one SELECT statement.

If you want to avoid a subquery, you could select the top 2, and skip the one you don't want. That kind of programming is pretty brittle, though. You have to remember what to skip every time; sooner or later, you'll forget.

Answer 17

Here is my code:

SELECT *  From
(select * from table name ORDER BY column name  DESC LIMIT 2) AS xyz                   
ORDER BY column name LIMIT 1;