Hello SQLteam,The subject was too short for me to be descriptive in regards of my question, I apologise..For SQL 2000 on a win2k SP2, I am paginating my result using the rowcount method of controlling my result pages, and forgoing temporary tables or table variables.Pagination works fine, if in the order by clause there is only 1 constraint, however, I am trying to achieve the effect of ranking by first membership, then the ID of each item. While IDcode is unique, membership has probably 5 distinct choices, and is in a different table so I couldn't combine indexes (and I don't think it helps either?)If I were to page 3 records at a time, 1st run I would getGold GGGGold FFFSilver AAA
And 2nd run I would get Gold GGGGold FFFSilver BBB
instead of theSilver AAASilver BBBSilver CCC
intended by pagination.If it was without the membership column, pagination would be very simple in this case, but what if an order by is required before the paging column?<pseudocode below>SET ROWCOUNT @startRowIndexselect @first_id = idcode from pseudotable order by membership, idcodeSET ROWCOUNT @NumRowsToBeFetchedselect * from pseudotable where idcode >=@first_id order by membership,idcodepseudotableGold GGGGold FFFSilver AAASilver BBBSilver CCC
temp tableless paging, with other order by
Posted - 2006-06-22 : 05:55:28
