CODEWITHSUNDEEP
sql-server
Atchoum
Atchoum

Reputation: 733

Offset Fetch strange glitch

Wish someone could explain to me how to avoid a problem which gave me a headache:


IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL BEGIN DROP TABLE #Temp END CREATE TABLE #Temp (
            Id uniqueidentifier,
            MemberId smallint,
            Description nvarchar(50),
            Credits smallint, 
            [Type] tinyint,
            CreateDate smalldatetime,
            ActionDate smalldatetime,
            Picture varchar(50),
            SortDate smalldatetime
            )
    
    INSERT INTO #Temp
    SELECT H.HistoryId
    , H.MemberId
    , H.Description
    , -H.Credits
    , 99
    , H.CreateDate
    , CI.Date
    , ''
    , CI.Date
    FROM Histories H
    INNER JOIN CourseInstances CI
    ON H.CourseInstanceId    =  CI.CourseInstanceId
    WHERE H.MemberId = '27'
    
    INSERT INTO #Temp
    SELECT P.PaymentId
    , P.MemberId
    , P.Description
    , P.Credits
    , P.PaymentType
    , P.CreateDate
    , P.ExpiryDate
    , P.Picture
    , P.CreateDate
    FROM Payments P
    WHERE P.MemberId = '27' DECLARE @COUNT int DECLARE @OFFSET int
     
    SELECT * from #Temp
    ORDER BY SortDate  OFFSET (@OFFSET) ROWS FETCH NEXT 10 ROWS ONLY

In the above code, I create a temp table to add 2 types of records coming from 2 different tables then I select a subset of these records based on a page size of 10.

Works great except for a small glitch.

In a specific case, I had 2 records with the same create date and they were in position 40 and 41. When I was asking for records 31 to 40, one of them was appearing in position 10 (ie. 40). When I was asking for records 41 to 50, the same record was appearing in position 1 (ie. 41). In other words, the other record was neither appearing in page 4 or 5...

I solved it by adding a sort on Id instead of just SortDate, ie changing the last line with:

ORDER BY SortDate, Id  OFFSET (@OFFSET) ROWS FETCH NEXT 10 ROWS ONLY

I am wondering why is this problem happening and if there is a better fix.

Upvotes: 0

Views: 162

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

I solved it by adding a sort on Id instead of just SortDate

This is the way. If you allow duplicates in your ORDER BY then which of the duplicate rows is returned in an OFFSET ... FETCH is undocumented behavior, and will depend on the query plan chosen.

Just like with a TOP values query without an ORDER BY, there is no ordering guarantee for rows except what you specify in your ORDER BY clause.

Upvotes: 2

Related Questions