CODEWITHSUNDEEP
sql-server-2000
Andreas
Andreas

Reputation:

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

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

Upvotes: 1

Views: 3177

Answers (5)

jveazey
jveazey

Reputation: 5458

Here's another example. This one uses a temporary table with an identity column.

SELECT TOP 5 IDENTITY(int) as id, col
  INTO #t
  FROM table
 ORDER BY col

SELECT col FROM #t WHERE id = 5

DROP TABLE #t

Upvotes: 0

Gordon Bell
Gordon Bell

Reputation: 13633

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

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171421

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

Upvotes: 0

Gordon Bell
Gordon Bell

Reputation: 13633

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
)

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415860

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.

Upvotes: 1

Related Questions