CODEWITHSUNDEEP
phpmysqlsql
Dev01
Dev01

Reputation: 4222

SQL order by issue - cannot order by column specified

I am not an expert in SQL therefore need your help.

I have this advanced query written by someone else:

SELECT cms_product_bid.*,
    cms_products.*,
    cms_products_images.pd_image, 
    cms_products_images.pd_image_label,
    IF (cms_product_bid.pd_bid = 1,
        IF(cms_product_bid.pd_bid_start_date <= CURDATE(),
            IF(cms_product_bid.pd_bid_end_date >= CURDATE(),1,0)
        ,0)
    ,1)  AS bid_product_check
FROM cms_products
LEFT JOIN cms_products_images 
ON (cms_products.pd_id = cms_products_images.pd_id)
INNER JOIN cms_product_bid 
ON (cms_product_bid.product_id = cms_products.pd_id)
INNER JOIN cms_home_product_slider 
ON (cms_products.pd_id = cms_home_product_slider.pd_id)
WHERE cms_products.pd_status=1 
    AND cms_products.pd_visibility=1 
    AND cms_home_product_slider.cat_id='$featured_cat_id'
GROUP BY cms_products.pd_id 
HAVING(bid_product_check =1)
ORDER BY cms_products.pd_sort ASC

All I am trying to do is to sort by cms_products.pd_sort but it isn't sorting by that column even though column is there in table with values such as 1, 2 and so on.

Can anyone point out the issue here ?

Upvotes: 0

Views: 73

Answers (2)

Randy
Randy

Reputation: 16677

it may be a bug... but try to list out the columns instead of using the .* shortcuts... then see if that column will be recognized and sorted correctly.

btw - using .* is not a best practice for many reasons.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

my guess is that your column is of type VARCHAR or string, you can simply CAST it

SELECT...
FROM...
WHERE...
ORDER BY CAST(cms_products.pd_sort AS SIGNED) ASC

Upvotes: 2

Related Questions