SQL Server – Select Last 2 Records Based on Previously Accessed Records

sql serversubquery

I have Message table

DDL

CREATE TABLE [dbo].[Message](
    [ID] [int] NOT NULL,
    [Message] [varchar](50) NOT NULL,
    [DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

DML

INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (1, N'M1', CAST(0x0000A7F400D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (2, N'M2', CAST(0x0000A7F400E6B680 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (3, N'M3', CAST(0x0000A7F500D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (4, N'M3', CAST(0x0000A7F600D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (5, N'M3', CAST(0x0000A7F700D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (6, N'M3', CAST(0x0000A7F800D63BC0 AS DateTime))
GO
INSERT [dbo].[Message] ([ID], [Message], [DateCreated]) VALUES (7, N'M3', CAST(0x0000A7F900D63BC0 AS DateTime))
GO

Table Message:

ID | Message | DateCreated 
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00 
3  | M3      | 2017-09-21 13:00:00
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

I have limited no of rows to be accessed , each time 2 rows.But based on number of row accessed , want to get next 2 rows.

For example:

1) 0 row accessed and get 2 rows:

ID | Message | DateCreated 
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

2) 2 rows accessed and now get next 2 rows:

ID | Message | DateCreated 
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00

3) 4 rows accessed and now get next 2 rows:

ID | Message | DateCreated 
2  | M2      | 2017-09-20 14:00:00 
3  | M3      | 2017-09-21 13:00:00

so on.

I may think of left join with itself and then use sub-query. But I m lost somewhere with my idea.

I may in other words as

select top 2 rows 
from   message 
where  ID not in (select * 
                  from   message 
                  where  rows already accessed ) 
order  by ID desc;

Its like pagination where I saw 2 records on each page. Unfortunately I will not provide the page number , instead I may say I have accessed rows with ID 7, 6 . Next two rows will be with ID 5, 4 and so on.

Best Answer

As far as I understood it would work as a pagination, and the params you would control on the app side anyways. I'd would the following:

@SQL2012:

SELECT [ID]
     , [message]
     , DateCreated
FROM [message]
ORDER BY ID DESC
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY