CODEWITHSUNDEEP
sqlsql-serversql-server-2012pagination
Ahmad Alkhawaja
Ahmad Alkhawaja

Reputation: 539

SQL Server 2012 Pagination

I have this table definition

CREATE TABLE [dbo].[Earnings](
    [Name] [nvarchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Earnings] [decimal](18, 2) NOT NULL
)

I have this sample data

INSERT INTO [dbo].[Earnings] ([name], [Date], [Earnings])
VALUES
('person1',  datefromparts(2015, 9, 1), 9.01),
('person1',  datefromparts(2015, 10, 5), 10.05),
('person1',  datefromparts(2015, 11, 21), 11.21),
('person1',  datefromparts(2016, 1, 11), 1.12),
('person1',  datefromparts(2016, 3, 13), 3.13),
('person1',  datefromparts(2016, 5, 15), 5.15),
('person2',  datefromparts(2016, 6, 16), 6.16),
('person2',  datefromparts(2016, 8, 18), 8.18)

When I do pagination I always get same result for the following two sql statements, I would like to know what is the reason for this issue ? and what possible ways to fix it ? 

SELECT  *  FROM [dbo].Earnings  order by Name   OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY ;
SELECT  *  FROM [dbo].Earnings  order by Name   OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY ;

The result I get for both execution

Name    Date    Earnings
person1 2015-11-21 00:00:00.000 11.21
person1 2015-10-05 00:00:00.000 10.05

Upvotes: 3

Views: 160

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28890

Order by should be deterministic .which means if any column contains same value and is used in order by clause,sql will pick/can pick any row.so to make order by deterministic ,order by columns should be unique.

In your case ,use

SELECT  *  FROM [dbo].Earnings  order by Name,date   OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY ;
SELECT  *  FROM [dbo].Earnings  order by Name,date   OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY ;

Upvotes: 5

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

Provided answer is correct. However, my suggestion is to add a PrimaryKey to your table and use that:

CREATE TABLE [dbo].[Earnings](
    EarningId INT NOT NULL CONSTRAINT PK_Earnings PRIMARY KEY,  -- can also be added IDENTITY(1, 1)
    [Name] [nvarchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Earnings] [decimal](18, 2) NOT NULL
)

The query becomes simple and I think also faster, as ordering will be done based on a clustered index:

SELECT * FROM [dbo].Earnings  order by EarningId OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
SELECT * FROM [dbo].Earnings  order by EarningId OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

Adding a PK also has the main advantage that each record is clearly identifiable.

As a note, before ordering by a column, I have also noticed that ordering by SELECT 1 seems to do the trick:

SELECT * FROM [dbo].Earnings  order by (SELECT 1) OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
SELECT * FROM [dbo].Earnings  order by (SELECT 1) EarningId OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

However, I would not rely on this ordering.

Upvotes: 2

Related Questions