Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    25

    Exclamation Unanswered: PostgreSQL ordering products incorrectly

    I started another thread on this forum where I received a lot of help on this problem, but never came to a final solution. The old thread is located here: http://www.dbforums.com/showthread.p...9&goto=newpost. I am posting this new thread to explain exactly where I am at on this problem so that people don't have to read through all the comments of the old thread to figure it out.

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to postgresql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way i think you are seeing what happens when there is no match in the LEFT OUTER JOIN

    that would make op.quantitySold NULL, and NULLs always sort last (or first, if DESC)

    dunno how you are managing to show 0 instead of NULL, though -- are you running this query in some kind of scripting language?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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