Thread: Display Products within a Category by SKU

We are trying to change the manner by which the products within a category are displayed from alphabetical to SKU/product number. Based on a previous post, we edited the stored proc aspdnsf_GetProducts. At the bottom part of the stored proc, you can see ORDER BY pf.rownum, change it to ORDER BY p.sku+pv.skusuffix (remove the +pv.skusuffix if you only want to sort it according to product sku).

However, we are having some problems with the products displaying in product number/SKU order versus alphabetically.

Here is what we are finding...most of the categories have enough products that there are multiple pages of products to be displayed. It seems that some things are in SKU order for the most part but then there are random products showing up in between...

For example: In an astrology category I have 12 products with the sku AST0010 thru AST0120. We also have a few other products from other categories showing up in this category as they represent astrological signs. The products are displaying in this order:

AST0010
AST0020
AST0030
FAS0460
OCC0270
GAR00480
AST0040
AST0050
AST0060
AST0070
AST0080
AST0090
AST0100
AST0110
AST0120

Any ideas of what's happening here?

There might be a trailing blanks before the SKU value on your products. Try using rtrim(ltrim(p.sku+pv.skusuffix)) to truncate it.