CODEWITHSUNDEEP
phpmysqlcodeigniter
Pran
Pran

Reputation: 1906

How to sort a table with specific/some rows always on top in Codeigniter?

How to code this query in Codeigniter using active record?

"SELECT * FROM `my_table` ORDER BY clm_name != 2 ASC"

enter image description here

Upvotes: 1

Views: 686

Answers (2)

1000111
1000111

Reputation: 13519

If you want to put pic_id = 2 on top always and prefer a natural order (ascending/descending) for the rest of the pic_ids then follow the query given below:

SELECT * 
FROM pictures
ORDER BY (pic_id <> 2) , pic_id ASC;

If you want to put pic_id (2) to the bottom always then:

SELECT * 
FROM pictures
ORDER BY (pic_id = 2) , pic_id ASC;

Note:

MySQL boolean expression resolves into 0/1. So for pic_id 2 (pic_id <> 2) returns 0 then the order by looks like ORDER BY 0, <pic_id> and the order by looks like ORDER BY 1, <pic_id> for the rest of the pic ids other than 2.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You should use an alias eg using a case statement for assigne the value you need to the alias column and the order by the alias name 

$this->db->select(' col1, col2 , 
        case clm_name when !=2 then 100 else clm_name end as my_clm_name');
$this->db->from('my_table');
$this->db->order_by('my_clm_name   asc');

Upvotes: 0

Related Questions