GROUP BY with DISTINCT LEFT

Hello,

Can anyone help me how to use GROUP BY with DISTINCT LEFT like this mysql query

SELECT DISTINCT LEFT(telephone, 6) FROM USER GROUP BY telephone;

GROUP BY working fine but I need left 6 number.

my action controller

public function actionRequest()

{


	$this->processAdminCommand();





	$criteria=new CDbCriteria;


   


	$criteria->group= 'telephone';


	$pages=new CPagination(USER::model()->count ($criteria)); 


      


	$pages->pageSize=self::PAGE_SIZE;


	$pages->applyLimit($criteria);





	$sort=new CSort('USER');


           $sort->applyOrder($criteria);





	$models=USER::model()->findAll($criteria);





	$this->render('request',array(


		'models'=>$models,


		'pages'=>$pages,


		'sort'=>$sort,


	));


}

you are unclear about your question

but I dare say

SELECT DISTINCT LEFT(telephone, 6) as xxx FROM USER;

"group by" is not necesary

count not work with group by and distinct

you have to do manual :(

see http://code.google.com/p/yii/issues/detail?id=675

and http://code.google.com/p/yii/source/detail?r=1563

and http://code.google.com/p/yii/issues/detail?id=694

greetings

Thanks for your reply, actually I want to use DISTINCT LEFT(telephone, 6) . Any idea.




public function actionRequest()

{

$this->processAdminCommand();


$criteria=new CDbCriteria;


$criteria->select= 'count(distinct LEFT(telephone, 6))';


$count=USER::model()->find($criteria)->queryScalar();




$criteria->distinct= true;

$criteria->select= 'LEFT(telephone, 6) as telephone6';


$pages=new CPagination($count); 


$pages->pageSize=self::PAGE_SIZE;

$pages->applyLimit($criteria);


$sort=new CSort('USER');

$sort->applyOrder($criteria);


$models=USER::model()->findAll($criteria);


$this->render('request',array(

'models'=>$models,

'pages'=>$pages,

'sort'=>$sort,

));

} 



and add at your model




public telephone6;



Thanks Buddy. You re great. Life is easy.