...

View Full Version : Database pagin problems



jaywhy13
08-26-2005, 01:32 AM
Shorthand:

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:

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

neocool00
08-26-2005, 03:27 PM
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.

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.

jaywhy13
08-26-2005, 04:14 PM
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 :confused:

ghell
08-26-2005, 05:59 PM
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:
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.

neocool00
08-26-2005, 07:35 PM
@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.

jaywhy13
08-26-2005, 10:04 PM
@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.... :D

@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 :eek:
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... :(

ghell
08-27-2005, 02:27 PM
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)

jaywhy13
08-28-2005, 05:20 PM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum