ORDER BY Problem with Joins

[rayfinkel2]

Here is my problem and it appears that it may only be a problem with PostgreSQL:

 

I am trying to order the products on my online store showing the products that have been sold the most amount of times within the last week on the top. 

 

There are a lot of products that may not have been sold within the last week, so may not appear in the orders_prods table.

 

This is how I want them to be ordered:

 

prod_id-----qtysold

ghi-----------15

abc----------10

def-----------7

lmo-----------0

pqr-----------0

xyz-----------0

 

 

 

Here is my current query:

 

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p INNER JOIN product_categories ON p.prod_id =

 

product_categories.prod_id LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

 

prod_id) AS op ON op.prod_id = p.prod_id WHERE product_categories.cat_id = '$catID' ORDER BY op.quantitySold ASC

 

This Query orders the products like this:

 

prod_id-----qtysold

def-----------7

abc----------10

ghi-----------15

lmo-----------0

pqr-----------0

xyz-----------0

 

 

When I order quantitySold by DESC, the products that have been sold recently always appear on the bottom no matter what I try.

 

Here is an example query:

 

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p INNER JOIN product_categories ON p.prod_id =

 

product_categories.prod_id LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY

 

prod_id) AS op ON op.prod_id = p.prod_id WHERE product_categories.cat_id = '$catID' ORDER BY op.quantitySold DESC, p.prod_id ASC

 

And I get this result:

 

prod_id-----qtysold

lmo-----------0

pqr-----------0

xyz-----------0

ghi-----------15

abc----------10

def-----------7

 

 

If anyone has any idea why this is happening, I would greatly appreciate the help.

 

      Thank You,

                    Kyle

[btherl]

Is that "0" or is it null?  Postgres places null as greater than all other values when ordering.  Some other DBMS will place null as lower than all others in the ordering.  To fix it, just replace your nulls with the value 0 and the ordering will be what you expect. 

 

SELECT op.quantitySold, p.thumbnail, p.main_image, p.prod_price, p.prod_id, p.prod_name FROM products AS p
INNER JOIN product_categories ON p.prod_id = product_categories.prod_id
LEFT OUTER JOIN (SELECT prod_id, SUM(quantity) AS quantitySold FROM orders_prods WHERE order_date > '$dateSevenDaysAgo' GROUP BY prod_id) AS op ON op.prod_id = p.prod_id
WHERE product_categories.cat_id = '$catID'
ORDER BY COALESCE(op.quantitySold, 0) ASC

 

Alternatively you can put that coalesce within the subquery and leave the order by as it was before.

 

Coalesce will return the first non-null argument.  So the call COALESCE(op.quantitySold, 0) will return op.quantitySold if it is not null, otherwise it will return 0.

[rayfinkel2]

Btherl, Your A Genius!!!!  It works!!!!    Thank you so much!!!!

 

        Take care,

                  Kyle