0

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:

Expected result

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
ratul keot
  • 29
  • 1
  • 12

2 Answers2

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.

Ajan Balakumaran
  • 1,639
  • 1
  • 8
  • 16
  • Ok, Understood. Is there any other way I can get my desired result?? – ratul keot Nov 04 '18 at 09:13
  • If you want those data then you can remove the offset and fetch next, or else offset 0 rows – Ajan Balakumaran Nov 04 '18 at 09:15
  • 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. – ratul keot Nov 04 '18 at 09:18
  • 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 – Ajan Balakumaran Nov 04 '18 at 09:21
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.

Antonín Lejsek
  • 6,003
  • 2
  • 16
  • 18
  • I have a total of 77 rows in the table. will that be enough?? – ratul keot Nov 04 '18 at 09:21
  • 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. – Antonín Lejsek Nov 04 '18 at 09:28
  • Is there any way I can fetch my records for pagination?? – ratul keot Nov 04 '18 at 09:32
  • 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. – Antonín Lejsek Nov 04 '18 at 09:33
  • 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?? – ratul keot Nov 04 '18 at 09:37
  • The query is correct. No, you can not get total number of pages in one call: https://stackoverflow.com/questions/818567/mysql-pagination-without-double-querying And finally, have a look at some tutorial about pagination. The basic approach most people use can cause missing or duplicate rows in the result as database row count change between calls for different pages. – Antonín Lejsek Nov 04 '18 at 10:25