SQL query with offset and fetch

Question

I am creating a basic ASP.net blog home page. I have two tables (structure given below). I need data from these tables for pagination so I used offset and skip in the query itself.

The tables are:

Table - Blog:

Id(primary),
Description,
Data,
CreatedById,
CreatedDate,
ImageUrl,
IsDeleted,
Tags,
StatusId(value = 1 for draft and 2 for publish)

Table - BlogCategories:

Id,
BlogId(foreign key to table 1),
CategoryId

I already have data in these tables. I am using the following query to fetch the data

Select *
from Blog b inner join
     BlogCategories bc
     on b.Id = bc.BlogId
where b.StatusId = 2 and bc.CategoryId = 2 and b.IsDeleted = 0
ORDER BY b.CreatedDate desc
OFFSET     73 ROWS      
FETCH NEXT 9 ROWS ONLY;

This query is returning 0 rows but if I remove the statusId and CategoryId from the where clause the the query returns certain number of rows.

The expected result is:

Am I doing something wrong or is there a better way of doing it. Please help me with this. Thank you.

What does this return: Select count(*) from Blog b inner join BlogCategories bc on b.Id = bc.BlogId where b.StatusId = 2 and bc.CategoryId = 2 and b.IsDeleted = 0 — Antonín Lejsek
– Antonín Lejsek, Commented Nov 4, 2018 at 8:53

Ajan Balakumaran · Accepted Answer · 2018-11-04 09:07:04Z

2

OFFSET is used to omit a specific number of rows and FETCH NEXT is used to get certain amount of rows that are coming right after the offset, here order by retains the order of rows so that you get precisely what you are expecting to get, so if your query is not giving an output then it means you dont have rows to get after offsetting 73 rows, in your case the query gives only 7 rows hence you don’t get an output.

answered Nov 4, 2018 at 9:07

Ajan Balakumaran

1,6491 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ratul keot Over a year ago

Ok, Understood. Is there any other way I can get my desired result??

Ajan Balakumaran Over a year ago

If you want those data then you can remove the offset and fetch next, or else offset 0 rows

ratul keot Over a year ago

I added offset because I want to paginate my records. 9 in each page. I guess it won't work for pagination if I remove the offset and fetch.

Ajan Balakumaran Over a year ago

Understood then offset 0 rows and fetch next 9 only for this special scenario or else anyways with the row count of 7 if you need to offset 73 you need more data so that you can off set and fetch

Antonín Lejsek · Accepted Answer · 2018-11-04 09:18:30Z

0

There are only 7 rows satisfying this condition

b.StatusId = 2 and bc.CategoryId = 2 and b.IsDeleted = 0

if you want to skip 73 rows of result and still get some rows, you would have to add more data to your database.

answered Nov 4, 2018 at 9:18

Antonín Lejsek

6,1132 gold badges18 silver badges18 bronze badges

6 Comments

ratul keot Over a year ago

I have a total of 77 rows in the table. will that be enough??

Antonín Lejsek Over a year ago

You have two tables. Aside from this, it depends on their statusId, CategoryId and IsDeleted status. You can have million rows, if they do not satisfy your filtering condition, you would never see them in the result.

ratul keot Over a year ago

Is there any way I can fetch my records for pagination??

Antonín Lejsek Over a year ago

Yes, I do not see where you have a problem. But you can not expect to get ten pages of results if you have only one page of data in the database.

ratul keot Over a year ago

One more thing, This query does not return the no of records fetched, which is required in order to make the total number of pages. Is it possible to get that in the one database call??

|

Collectives™ on Stack Overflow

SQL query with offset and fetch

2 Answers 2

4 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related