T-SQL How to select only Second row from a table?

Question

I have a table and I need to retrieve the ID of the Second row. How to achieve that ?

By Top 2 I select the two first rows, but I need only the second row

Answer 1

In SQL Server 2012+, you can use OFFSET...FETCH:

SELECT
   <column(s)>
FROM
   <table(s)>
ORDER BY
   <sort column(s)>
OFFSET 1 ROWS   -- Skip this number of rows
FETCH NEXT 1 ROWS ONLY;  -- Return this number of rows

Answer 2

Assuming SQL Server 2005+ an example of how to get just the second row (which I think you may be asking - and is the reason why top won't work for you?)

set statistics io on

;with cte as
(
  select *
    , ROW_NUMBER() over (order by number) as rn
  from master.dbo.spt_values
) 
select *
from cte
where rn = 2

/* Just to add in what I was running RE: Comments */
;with cte as
(
  select top 2 *
    , ROW_NUMBER() over (order by number) as rn
  from master.dbo.spt_values
) 
select *
from cte
where rn = 2

Answer 3

No need of row number functions if field ID is unique.

SELECT TOP 1 *
FROM (
  SELECT TOP 2 * 
  FROM yourTable
  ORDER BY ID
) z
ORDER BY ID DESC

Answer 4

Use ROW_NUMBER() to number the rows, but use TOP to only process the first two.

try this:

DECLARE @YourTable table (YourColumn int)
INSERT @YourTable VALUES (5)
INSERT @YourTable VALUES (7)
INSERT @YourTable VALUES (9)
INSERT @YourTable VALUES (17)
INSERT @YourTable VALUES (25)

;WITH YourCTE AS
(
  SELECT TOP 2
    *, ROW_NUMBER() OVER(ORDER BY YourColumn) AS RowNumber
  FROM @YourTable
) 
SELECT *
FROM YourCTE
WHERE RowNumber=2

OUTPUT:

YourColumn  RowNumber
----------- --------------------
7           2

(1 row(s) affected)

Answer 5

you can use OFFSET and FETCH NEXT

SELECT id
FROM tablename
ORDER BY column
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

NOTE:

OFFSET can only be used with ORDER BY clause. It cannot be used on its own.

OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.

The OFFSET argument is used to identify the starting point to return rows from a result set. Basically, it exclude the first set of records.

The FETCH argument is used to return a set of number of rows. FETCH can’t be used itself, it is used in conjuction with OFFSET.

Answer 6

I'm guessing you're using SQL 2005 or greater. 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 

Answer 7

with T1 as
(
  select row_number() over(order by ID) rownum, T2.ID
  from Table2 T2
)
select ID
from T1
where rownum=2

Answer 8

Use TOP 2 in the SELECT to get the desired number of rows in output. This would return in the sequence the data was created. If you have a date option you could order by the date along with TOP n Clause.

To get the top 2 rows;

SELECT TOP 2 [Id] FROM table 

To get the top 2 rows order by some field

SELECT TOP 2 [ID] FROM table ORDER BY <YourColumn> ASC/DESC

To Get only 2nd Row;

WITH Resulttable AS 
( 
  SELECT TOP 2 
    *, ROW_NUMBER() OVER(ORDER BY YourColumn) AS RowNumber 
  FROM @Table 
)  
SELECT *
FROM Resultstable
WHERE RowNumber = 2

Answer 9

Select top 2 [id] from table Order by [id] desc should give you want you the latest two rows added.

However, you will have to pay particular attention to the order by clause as that will determine the 1st and 2nd row returned.

If the query was to be changed like this:

Select top 2 [id] from table Order by ModifiedDate desc

You could get two different rows. You will have to decide which column to use in your order by statement.

Answer 10

I have a much easier way than the above ones.

DECLARE @FirstId int, @SecondId int

    SELECT TOP 1 @FirstId = TableId from MyDataTable ORDER BY TableId 
    SELECT TOP 1 @SecondId = TableId from MyDataTable WHERE TableId <> @FirstId  ORDER BY TableId 

SELECT @SecondId 

Answer 11

SELECT *
FROM (
  SELECT top 3 *
    , ROW_NUMBER() OVER (ORDER BY [newsid] desc) AS Rownumber
  FROM news
  where (news_type in(2,12))
) results
WHERE results.Rownumber = 1

// news table name and newsid column name

Answer 12

Certainly TOP will surfice if you simply want the TOP 2, but if you need them individually so that you can do something with those values then use the ROW_NUMBER which will give you more control over the rows you want to select

ps. I did this as i'm not sure if the OP is after a simple TOP 2 in a select. (I may be wrong!)

-- Get first row, same as TOP 1
SELECT [Id] FROM 
(
    SELECT [Id], ROW_NUMBER() OVER (ORDER BY [Id]) AS Rownumber
    FROM table
) results
WHERE results.Rownumber = 1

-- Get second row only
SELECT [Id] FROM 
(
    SELECT [Id], ROW_NUMBER() OVER (ORDER BY [Id]) AS Rownumber
    FROM table
) results
WHERE results.Rownumber = 2

Answer 13

This is also useful:

SELECT t.*
FROM (
  SELECT e1.*
    , row_number() OVER (ORDER BY e1.Rate DESC) AS _Rank
  FROM
  HumanResources.EmployeePayHistory AS e1
) AS t
WHERE t._Rank = 2

Answer 14

There is a relative simple solution based on comments from SQLDiver and Taha Ali.

Imagine there is a string containing 'domain\username' retrieved by function ORIGINAL_LOGIN() and i don't need the domain component.

First approach

DECLARE @Login NVARCHAR(100)
SELECT @Login = 'domain\username'
-- SELECT @Login = ORIGINAL_LOGIN()

SELECT
    VALUE
FROM
    STRING_SPLIT(@Login, '\')

returns a table having 2 rows. First row holds the domain and second row holds the requested username.

Using

DECLARE @Login NVARCHAR(100)
SELECT @Login = 'domain\username'
-- SELECT @Login = ORIGINAL_LOGIN()

SELECT
    VALUE
FROM
    STRING_SPLIT(@Login, '\')
ORDER BY
    (SELECT NULL)
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

returns exactly the second row from the temporary table.

Answer 15

Another idea is this:

 SELECT MIN(id) FROM
     (
      SELECT TOP 2(TAB.[id])
          FROM TAB 
          where TAB.field1 =3
           ORDER BY TAB.[creationDate] DESC
          ) AS TEST
     )

Answer 16

Just to add another option (one that doesn't use ORDER BY at all):

SELECT TOP 2
    t2.*
FROM YourTable t2
EXCEPT
SELECT TOP 1
    t1.*
FROM YourTable t1;

Answer 17

select *
from (
  select ROW_NUMBER() OVER (ORDER BY Column_Name) as ROWNO, *
  from Table_Name
) Table_Name
where ROWNO = 2

Answer 18

SELECT TOP 2 [Id] FROM table