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

Question

Hi, I have a table and I need retrieve the ID of the First and Second row. How to achieve that ? By Top 2 I select two first rows, but I need only second row

Answer 1

You can use select top 2 Id from yourtable.

Answer 2

SELECT TOP 2 [Id] FROM table

Answer 3

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 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 4

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 5

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 6

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 7

I'm guessing you're using SQL 2005 or greater. The 2nd line selects the top 2 rows by using 'ORDER BY ROW_COUNT DESC". Since I used "DESC", the 2nd row is arranged as being first.

SELECT TOP 1 COLUMN1, COLUMN2 from (
SELECT TOP 2 COLUMN1, COLUMN2 FROM Table ORDER BY ROW_NUMBER DESC )

Answer 8

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

Answer 9

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)