Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Database pagin problems

    Shorthand:
    Code:
    SELECT TOP 7 h.aId, h.typeId, h.aTitle, h.aDateField, h.aAuthor, h.aIcon, h.blnIcon, h.aUser.
    ....
    WHERE h.aId NOT IN (SELECT TOP 7 aId FROM headers ORDER BY h.aTitle ASC ) ORDER BY h.aTitle ASC
    Problem:
    When it moves from the first page, it skips a record when starting on the new page.

    If I try to reduce the second number... as in, the "NOT IN" part by one.. it puts one too many.

    Here's an exmaple of the full sql statement:
    Code:
    SELECT TOP 7 h.aId, h.typeId, h.aTitle, h.aDateField, h.aAuthor, h.aIcon, h.blnIcon, h.aUser, (SELECT TOP 1 t.typeName FROM types t WHERE t.typeId = h.typeId), (SELECT TOP 1 f.folderName FROM folders f WHERE f.folderId = (SELECT folderId FROM types WHERE types.typeId = h.typeId)), (SELECT COUNT (*) FROM publishedArticles pA WHERE pA.aId = h.aId) AS paCount, (SELECT COUNT (*) FROM mediaGallery mG WHERE mG.aId = h.aId) AS mediaCount, (SELECT COUNT (*) FROM publishedArticles) FROM headers h WHERE h.aId NOT IN (SELECT TOP 7 aId FROM headers ORDER BY h.aTitle ASC ) ORDER BY h.aTitle ASC
    Last edited by jaywhy13; 08-26-2005 at 04:17 AM.
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #2
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not sure if this will make a difference, but it's just something I noticed. You should probably remove the "h." off of the aTitle in your not in clause.
    Code:
    WHERE h.aId NOT IN (SELECT TOP 7 aId FROM headers ORDER BY h.aTitle ASC ) ORDER BY h.aTitle ASC
    Other than that, it looks like a good statement.

  • #3
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Code:
    SELECT TOP 8 h.aId, h.typeId, h.aTitle, h.aDateField, h.aAuthor, h.aIcon, h.blnIcon, h.aUser, (SELECT t.typeName FROM types t WHERE t.typeId = h.typeId), (SELECT TOP 1 f.folderName FROM folders f WHERE f.folderId = (SELECT folderId FROM types WHERE types.typeId = h.typeId)), (SELECT COUNT (*) FROM publishedArticles pA WHERE pA.aId = h.aId) AS paCount, (SELECT COUNT (*) FROM mediaGallery mG WHERE mG.aId = h.aId) AS mediaCount, (SELECT COUNT (*) FROM headers) FROM headers h WHERE h.aId NOT IN (SELECT TOP 8 aId FROM headers ORDER BY h.aTitle  ASC ) ORDER BY h.aTitle  ASC
    Thats what I have... and that doesn't work
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #4
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    he was saying this pretty much:

    SELECT TOP 8 aId FROM headers ORDER BY h.aTitle

    thats your subquery.. h is part of the external query so there is no h.aTitle

    as it is a subquery it ignores the external query if it can so you can just use

    SELECT TOP 8 aId FROM headers ORDER BY aTitle

    the basic theory of this type of paging (see that pagination thread) is this

    select top x ... from ... where ... and id not in (select top x*y ... from ... where ...)

    where x is number of records per page and y is page number. it usually needs to have exactly the same statement but times page number from the top (eg page 1 is *0) ms access complains about top 0 but mssql doesnt..

    it might be a good idea to put the whole data into a view and thhen use this:
    Code:
    select top " & intNoPerPage & " * from view1 where ... and id not in (select top " & (intNoPerPage*(intPageNo-1)) & " id from view1 where ...)
    where ... is the same in both places, however there are some times views cannot be used.

  • #5
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @jay,
    I think this is also going to correspond with your other thread that I just posted on. I'm starting to see what you are wanting to do and I don't think you are going to be able to do it one sql statement. You are either going to have to do it in two sql statements or a stored procedure. The reason being is that you can not use "ORDER BY" in a subquery. So you would have to do one sql call to get the ids that you don't want to use and then pass them into the second sql call.

  • #6
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    @jay,
    I think this is also going to correspond with your other thread that I just posted on. I'm starting to see what you are wanting to do and I don't think you are going to be able to do it one sql statement. You are either going to have to do it in two sql statements or a stored procedure. The reason being is that you can not use "ORDER BY" in a subquery. So you would have to do one sql call to get the ids that you don't want to use and then pass them into the second sql call.
    @neocool00
    Thanks... for lettting me know that. I guess I can sacrifice another quick call....

    @ghell
    Thanks also for your reply... I read the pagination thread and as far as I can see I think I followed the instructions ok... Did I?
    I started countin pages at 1, not zero...
    And I adjusted my code accordingly...

    HOwever.. I'm still left with the same problem
    If I dislpay 10 records per page.. all will show.
    But if I display 5 records per page and go to page 2...
    It starts displaying at record #7...
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #7
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    get it to write out your sql, it should say this:

    10 per page, page 2: top 10 where not in top 10
    10 per page, page 3: top 10 where not in top 20
    5 per page, page 2: top 5 where not in top 5
    5 per page, page 3: top 5 where not in top 10

    appart from the top# and the "where not in" it should be exactly the same sql in both parts (the get records part and the where clause part)

  • #8
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    So done.... (there are 20 records... it goes to 19 whenever I display under 20 records per page... If I set the page display I see the 20th record)
    Results:
    NB: If the page is at 1, I don't add the select id not in.. blah blah.
    2 per page, page 1: top 2 where not in top 0
    2 per page, page 2: top 2 where not in top 2
    2 per page, page 3: top 2 where not in top 4
    2 per page, page 4: top 2 where not in top 6
    2 per page, page 5: top 2 where not in top 8
    ..
    ..
    ...

    5 per page, page 1: top 5 where not in top 0
    5 per page, page 2: top 5 where not in top 5
    5 per page, page 3: top 5 where not in top 10
    5 per page, page 4: top 5 where not in top 15


    10 per page, page 1: top 10 where not in top 0
    10 per page, page 2: top 10 where not in top 10
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •