1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Order by greatest (1, If(a,0,b)) desc

Discussion in 'General PHP and MySQL Discussions' started by tenants, Sep 19, 2011.

  1. tenants

    tenants Well-Known Member

    I'm using the following order stament for one of my plugins:

    Order by GREATEST (1, IF(sft.king_pinned_bid IS NULL, 0, sft.king_pinned_bid)) DESC
    A plugin user found that their database returned the following error:
    "Mysqli prepare error: FUNCTION scenemar_xenforo.GREATEST does not exist"

    This to me implies their database doesn't support the GREATEST function, or using it in this way

    Is GREATEST, or using GREATEST in this way not supported by all versions of MySQL?
  2. Brogan

    Brogan XenForo Moderator Staff Member

    tenants likes this.
  3. tenants

    tenants Well-Known Member

    Yup, I was just looking at that at the same time "Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL"
    The thing is, the way I'm using it here, it will always be

    Order by GREATEST (1, 0) // ( if king_pinned_bid IS NULL)
    Order by GREATEST (1, sft.king_pinned_bid) // ( if king_pinned_bid IS not NULL)

    So this shouldnt be an issue anyway
  4. Pepelac

    Pepelac Well-Known Member

    Just remove space between GREATEST and (
    tenants likes this.
  5. tenants

    tenants Well-Known Member

    Woah, that could be it!
    I'll get them to give it a go, if it works then I will update the plugin (it is a bit strange having a space there, but I didnt see any issues on 2 databases that I tried)

Share This Page