7

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

Bruno 'Shady'
  • 4,348
  • 13
  • 55
  • 73
  • 4
    possible duplicate of [How to select the nth row in a SQL database table?](http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table) and [many others](http://stackoverflow.com/search?q=sql+select+nth+from+top) – Matt Ball Jan 06 '12 at 01:51

5 Answers5

14

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.

Seramme
  • 1,340
  • 7
  • 9
8

Select first row:

select ... order by some_rule limit 1;

Select second row:

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

Yixian
  • 81
  • 1
  • 2
2

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
Bendy
  • 3,506
  • 6
  • 40
  • 71
Frank
  • 21
  • 2
0

To get the second row from top you can use

 SELECT c1 , c2 , c3
 FROM table
 ORDER BY c1 OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
sebeid
  • 121
  • 8
-1

SELECT A.CName FROM (SELECT cname from Tname ORDER BY cname desc FETCH FIRST 2 ROWS ONLY) As A order by A.cname fetch first 1 row only;

where CNAME = column you want to refer , Tname = table name from which u want to pull. here u can replace the value of 2 with the row u want to fetch..

user5466439
  • 11