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

Question

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

Answer 1

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.

Answer 2

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
)

Answer 3

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

Answer 4

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

Answer 5

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