1

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.

  • 2
    Please, try to clarify it. It is not clear how do you access to this rows. – McNets Oct 2 '17 at 8:25
  • Please, check again. I have added more explanation @McNets – Hina Khuman Oct 2 '17 at 8:33
  • 2
    Give us DDL (CREATE TABLE...), DML (INSERT INTO mytable VALUES (...), the result you want and the logic you used to get it. Your attempts at solving the problem also. – Vérace Oct 2 '17 at 8:44
  • @Vérace I am stuck with the fact that I want to access next 2 rows based on previously accessed row.I m in this logic.and so I asked here – Hina Khuman Oct 2 '17 at 8:55
  • Maybe you're looking at some sort of tree type structure - using WITH RECURSIVE? – Vérace Oct 2 '17 at 8:58
1

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
0

I think this is solution for your case.

SELECT TOP 2 *
FROM [dbo].[Message]
WHERE ID < (SELECT ISNULL(MIN(ID),2147483647) FROM [dbo].[Message] WHERE <predicate>)
ORDER BY ID DESC

For example we can use DateCreated column to filter rows and check examples which you provided.

  1. 0 row accessed and get 2 rows:

    SELECT TOP 2 *
    FROM [dbo].[Message]
    WHERE ID < (SELECT ISNULL(MIN(ID),2147483647) FROM [dbo].[Message] WHERE DateCreated > '20170930')
    ORDER BY ID DESC
    
  2. 4 rows accessed and now get next 2 rows:

    SELECT TOP 2 *
    FROM [dbo].[Message]
    WHERE ID < (SELECT ISNULL(MIN(ID),2147483647) FROM [dbo].[Message] WHERE DateCreated > '20170924')
    ORDER BY ID DESC
    
  3. 0 row accessed and get 2 rows:

    SELECT TOP 2 *
    FROM [dbo].[Message]
    WHERE ID < (SELECT ISNULL(MIN(ID),2147483647) FROM [dbo].[Message] WHERE DateCreated > '20170922')
    ORDER BY ID DESC
    

Magic number is the maximum of INT data type to manage no rows returned scenario.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.