How select second line from top 2 or something similar?

Question

I want to execute 2 separated commands to return me a value from my table.

the first one could be top 1, because is the first line, no problem...

but how can I make something like top 2, but only showing the second line?

Is there a simple way to do it? Like one simple select?

1 line:

select top 1 Code from Products order by LastUpdate desc

Answer 1

There is no generic solution to this problem, as far as I know - different DBMSes have different way of achieving this.

In Microsoft SQL Server, you can use the ROW_NUMBER clause:

SELECT code FROM 
    (SELECT TOP 2 code, Row_Number() OVER (ORDER BY lastupdate) AS rownum
     FROM Products) AS tbl
WHERE rownum = 2;

Oracle has a similar pseudo-column, called ROWNUM. However, the caveat here is that this value is computed before the ordering comes into play. Therefore, you would have to, once again, use a subquery:

SELECT code FROM
    (SELECT code, ROWNUM rnum FROM
        (SELECT code FROM Products ORDER BY lastupdate) 
     WHERE ROWNUM <= 2)
WHERE rnum = 2

Note that you cannot do a simple ROWNUM = 2 condition here, because it would never be satisfied - ROWNUM takes into account the number of actually returned rows, so if there never was a first returned row, ROWNUM will never reach the value '2', thus will never satisfy the condition.

In MySQL, this is even simpler:

SELECT code FROM Products ORDER BY lastupdate LIMIT 2, 1

(I am not familiar with MySQL, so I am not sure if the LIMIT will be calculated before or after the ORDER BY clause - would be great if someone else could confirm this).

Other DBMSes do it in an even different way.

Answer 2

Select first row:

select ... order by some_rule limit 1;

Select second row:

select ... order by some_rule limit 1 offset 1;

Answer 3

To me in MS-SQL, this is simpler to remember:

Select top N rows order desc as a "table" then select top 1 order asc

SELECT TOP 1 code FROM 
    (SELECT TOP 2 code, lastupdate  FROM Products ORDER BY lastupdate DESC) AS tblTempQuery
ORDER BY lastupdate ASC