0

In my application that using MSAccess as DB, I need to implement pagewise loading with sql query.

I know how to load first 10 records, thats by

SELECT TOP 10 * FROM Product ORDER BY dateAdded DESC

But how can I pick record that is from 10 to 20.

Any Idea?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Johnykutty
  • 12,091
  • 13
  • 59
  • 100

1 Answers1

1

It's possible in Access SQL, but not as straightforward as in other database products.
(for example MySQL, where it would be just LIMIT 10,10)

Check out my answer here:
How to do MS Access database paging + search?

(the code to build the SQL Statement is in C#, but of course you can do it in any other language as well. If you don't know C# and need help understanding my answer, just leave a comment here)

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • I have created a query with yyour sample "select * from Product where dateAdded in (select top 10/*page size*/ sub.dateAdded" from ( select top 3*10 tab.dateAdded from Product tab where catID = 100 order by tab.dateAdded ) sub order by sub.dateAdded desc ) order by dateAdded"; is this correct – Johnykutty Jul 22 '13 at 07:04
  • 1
    This part looks strange: `top 10/*page size*/ ` Is "page size" a comment? If yes, remove it. Plus, you should calculate `PageNum * PageSize`, i.e. the finished SQL string should read `30`, not `3*10`. – Christian Specht Jul 22 '13 at 17:33
  • ok, But I cant edit the comment of your answer so adding new comment "select * from Product where dateAdded in (select top 10 sub.dateAdded" from ( select top 30 tab.dateAdded from Product tab where catID = 100 order by tab.dateAdded ) sub order by sub.dateAdded desc ) order by dateAdded" is this correct if 10 is page size and 3 is page number? – Johnykutty Jul 23 '13 at 04:40
  • The quotes after `select top 10 sub.dateAdded" ` shouldn't be there. Apart from this, the query looks correct. Why don't you just run it and see if it works? – Christian Specht Jul 23 '13 at 05:29
  • I'm an ios developer, in my webservice the querry is passing to server, then result dataset will get as a result – Johnykutty Jul 23 '13 at 08:47