How to get only second,third,fourth or fifth row of a query result?(SQL SERVER 2000)

Hi,

I would need a suggestion or even a solution, how to get only the second,third... row of a query result with high performance. I know there is the possibility of row_number(SQL Server 2005 or higher) but not for SQL Server 2000 :-(

My first try to get the fifth row was:
SELECT TOP 1 col
FROM (SELECT TOP 5 col
FROM table
ORDER BY col) q
ORDER BY col DESC

I try to explain my requirements: In my table there can be max. 5 rows for one person. But if only 4 rows for this person exists I would get a wrong result with my above query.

Any suggestions?

Any help will be appreciated!

Thx forward, Best Regards Andreas

From stackoverflow

• Probably the best and fastest way to do this is just select the top 5 in a datareader, and just read the next into memory as you need it.

• To get the 5th Row, you can use multiple subqueries like:

  select top 1 MyCol from MyTable
  where MyCol = 
  (
      select top 1 MyCol from 
      (
          select top 5 MyCol from MyTable order by MyCol
      ) t order by MyCol desc
  )
  

• If I understand correctly, you only want the row returned if there actually is a 5th row, for example. In that case, you can do this:

  SELECT TOP 1 col
  FROM (
      SELECT TOP 5 col
      FROM table
      where (SELECT COUNT(*) FROM table) >= 5
      ORDER BY col
  ) q
  ORDER BY col DESC
  

• Hi,

  thank you all for your replies.
  I'll try explain more detail of my requirements,so hopefully all questions were answered. Currently I have a Table named USER(count of Users ~ 12000) where all the skills of the Users are available. As I wrote in my first post, there can be up to 5 rows for one User. Now I need to select the rows for a User and insert them in a new table, where only 1 row for a User is possible. Hopefully this example will help to understand my needs:
  

  
  Example:
  Current Table User
  UserId;Skills
  4679;A
  4679;B
  4679;C
  4679;D
  4679;E
  
  New Table User:
  UserId;Skills1;Skills2;Skills3;Skills4;Skills5
  4679;A;B;C;D;E
  
  P.S.: The Solution of OrbMan is a good approach...

  Any other suggestions?(It can also be a solution within a stored procedure..)

  Any help will be appreciated!

  Thx forward, Best Regards Andreas

• This should do it...

  CREATE PROCEDURE [dbo].[sp_UpdateSkills]
  AS
  BEGIN
      set nocount on
  
      declare @UserID int
      declare @Skill varchar(1)
      declare @SkillCount int
  
      declare @Skill1 varchar(1)
      declare @Skill2 varchar(1)
      declare @Skill3 varchar(1)
      declare @Skill4 varchar(1)
      declare @Skill5 varchar(1)
  
      declare csrUser cursor for
       select distinct UserID
       from dbo.tblSkills1
       order by 1
  
      open csrUser
  
      fetch next from csrUser into @UserID
      while (@@fetch_status = 0)
      begin
       declare csrSkill cursor for
        select Skill
        from dbo.tblSkills1
        where UserID = @UserID
        order by Skill
  
       set @SkillCount = 1
       set @Skill1 = null
       set @Skill2 = null
       set @Skill3 = null
       set @Skill4 = null
       set @Skill5 = null
  
       open csrSkill
  
       fetch next from csrSkill into @Skill
       while (@@fetch_status = 0 and @SkillCount < 6)
       begin
        print @UserID
        print @Skill
  
        if (@SkillCount = 1)
         set @Skill1 = @Skill
        else if (@SkillCount = 2)
         set @Skill2 = @Skill
        else if (@SkillCount = 3)
         set @Skill3 = @Skill
        else if (@SkillCount = 4)
         set @Skill4 = @Skill
        else if (@SkillCount = 5)
         set @Skill5 = @Skill
  
        set @SkillCount = @SkillCount + 1
  
        fetch next from csrSkill into @Skill
       end
  
       close csrSkill
       deallocate csrSkill
  
       insert into tblSkills2
       (UserID, Skill1, Skill2, Skill3, Skill4, Skill5)
       values
       (@UserID, @Skill1, @Skill2, @Skill3, @Skill4, @Skill5)
  
       fetch next from csrUser into @UserID
      end
  
      close csrUser
      deallocate csrUser
  END