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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt   FirstId  ORDER BY TableId   SELECT  SecondId

User · Answer

SELECT TOP 2  Id  FROM table

User · Answer

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

User · Answer

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

User · Answer

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  lt YourColumn gt  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

User · Answer

In SQL Server 2012   you can use OFFSET   FETCH   SELECT     lt column s  gt  FROM     lt table s  gt  ORDER BY     lt sort column s  gt  OFFSET 1 ROWS   -- Skip this number of rows FETCH NEXT 1 ROWS ONLY   -- Return this number of rows

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select   from     select ROW NUMBER   OVER  ORDER BY Column Name  as ROWNO      from Table Name   Table Name where ROWNO   2

User · Answer

with T1 as     select row number   over order by ID  rownum  T2 ID   from Table2 T2   select ID from T1 where rownum 2

[sql-server] T-SQL How to select only Second row from a table?

Examples related to sql-server

Examples related to tsql