complex MySQL Order by not working

Here is the select statement I'm using. The problem happens with the sorting. When it is like below, it only sorts by t2.userdb_user_first_name, doesn't matter if I put that first or second. When I remove that, it sorts just fine by the displayorder field value pair. So I know that part is working, but somehow the combination of the two causes the first_name to override it. What I want is for the records to be sorted 开发者_开发技巧by displayorder first, and then first_name within that.

SELECT t1.userdb_id
FROM default_en_userdbelements as t1
  INNER JOIN default_en_userdb AS t2 ON t1.userdb_id = t2.userdb_id
WHERE t1.userdbelements_field_name = 'newproject'
  AND t1.userdbelements_field_value = 'no'
  AND t2.userdb_user_first_name!='Default'
ORDER BY
  (t1.userdbelements_field_name = 'displayorder' AND t1.userdbelements_field_value),
  t2.userdb_user_first_name;

Edit: here is what I want to accomplish. I want to list the users (that are not new projects) from the userdb table, along with the details about the users that is stored in userdbelements. And I want that to be sorted first by userdbelements.displayorder, then by userdb.first_name. I hope that makes sense? Thanks for the really quick help!

Edit: Sorry for disappearing, here is some sample data

userdbelements

userdbelements_id   userdbelements_field_name   userdbelements_field_value  userdb_id
647 heat        1
648 displayorder    1 - Sponsored   1
645 condofees       1

userdb

userdb_id   userdb_user_name    userdb_emailaddress userdb_user_first_name  userdb_user_last_name
10  harbourlights   info@harbourlightscondosminium.ca   Harbourlights   1237 Northshore Blvd, Burlington
11  harbourview info@harbourviewcondominium.ca  Harbourview 415 Locust Street, Burlington
12  thebalmoral info@thebalmoralcondominium.ca  The Balmoral    2075 & 2085 Amherst Heights Drive, Burlington

You are trying to use an invalid ORDER BY.

ORDER BY (t1.userdbelements_field_name = 'displayorder' AND t1.userdbelements_field_value)

It must reference a table column or returned aliased column.

I really cannot follow how this query would even be possible as you already have limited t1.userdbelements_field_name = newproject and then you wish to order by the case of it being equal to displayorder.

Could you please modify your question to state exactly what it is that you are trying to accomplish in your order by clause?

From what I understand, you'd have to join to default_en_userdbelements solely for the displayorder value. However, I suspect there's something wrong with your query and that it probably returns duplicate values for userdb_id.

Perhaps you should say what you're trying to actually do, not explain the way you're trying to do it.

  SELECT t1.userdb_id
    FROM default_en_userdbelements AS t1
    JOIN default_en_userdb         AS t2 ON t1.userdb_id = t2.userdb_id

    JOIN default_en_userdbelements AS o  ON (o.userdb_id,  o.userdbelements_field_name)
                                          = (t1.userdb_id, 'displayorder')

   WHERE t1.userdbelements_field_name   = 'newproject'
     AND t1.userdbelements_field_value  = 'no'
     AND t2.userdb_user_first_name     != 'Default'

ORDER BY o.userdbelements_field_value,
         t2.userdb_user_first_name

You could do something like this:

ORDER BY 
  (CASE WHEN t1.userdbelements_field_name = 'displayorder' 
  THEN t1.userdbelements_field_value 
  ELSE $some_large_number 
  END),
 t2.userdb_user_first_name;

It sorts using the value of t1.userdbelements_field_value when t1.userdbelements_field_name = 'displayorder', but you have to supply some other value of the same type to apply for the ELSE.