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: