CODEWITHSUNDEEP
mysqlzend-framework
Sipan
Sipan

Reputation: 221

How to order including word order in SQL

Here is my query:

$select = $this->_conn->select();
    $select->from(array('usr' => $this->_prefix . 'user'));
    $select->join(array('rol' => 'role'), 'usr.role_id = rol.role_id',array());
    $select->join(array('law' => 'lawyer_details'), 'usr.user_id = law.user_id', array('lawyer_url'));
    $select->where("usr.user_status = ?","yes");
    $select->where("rol.role_key = ?","yes");
    $select->where("usr.user_section_show = ?","yes");
    $select->where("usr.managment_show = ?","yes");
    $select->limit(4);
    $select->order(array('usr.user_firstname ASC'));
    $rs = $select->query()->fetchAll();
    return new Obt_Model_RecordSet($rs, $this);

I want to order by user_firstname but first get that user which names are "xxx","yyy","lll";

Upvotes: 2

Views: 72

Answers (3)

Sipan
Sipan

Reputation: 221

->order(new Zend_Db_Expr("FIELD(usr.user_firstname, 'xxx','yyy','lll')"));

Upvotes: 1

Jiri Tousek
Jiri Tousek

Reputation: 12440

In standard SQL, you may use:

order by
  (case when usr.user_firstname in ('xxx', 'yyy', 'III') then 1 else 0 end) DESC,
  usr.user_firstname ASC

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try this :-

order by (user_firstname='xxx') DESC,user_firstname ASC

Upvotes: 0

Related Questions