How can get unique values from data table using dql?

doctrine, dql, mysql

Solution

Depends on what version you are using, but I had the same issue and ->distinct() worked for me.

Doctrine_Query::create()
      ->select('rec.city')->distinct()
      ->from('Records rec')                   
      ->where("rec.state = '$state'")                                    
       ->execute();

Problem

I am having a table in which there is a column in which various values are stored.i want to retrieve unique values from that table using dql. ``` Doctrine_Query::create() ->select('rec.school') ->from('Records rec') ->where("rec.city='$city' ") ->execute(); ``` Now i want only unique values. Can anybody tell me how to do that... Edit Table Structure: ``` CREATE TABLE IF NOT EXISTS `records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `school` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16334 ; ``` This is the Query I am using: ``` Doctrine_Query::create() ->select('DISTINCT rec.city') ->from('Records rec') ->where("rec.state = '$state'") // ->getSql(); ->execute(); ``` Generting Sql for this gives me: ``` SELECT DISTINCT r.id AS r__id, r.city AS r__city FROM records r WHERE r.state = 'AR' ``` Now check the sql generated:::: DISTINCT is on 'id' column where as i want Distinct on city column. Anybody know how to fix this. EDIT2 Id is unique cause its an auto incremental value.Ya i have some real duplicates in city column like: Delhi and Delhi. Right.. Now when i am trying to fetch data from it, I am getting Delhi two times. How can i make query like this: ``` select DISTINCT rec.city where state="xyz"; ``` Cause this will give me the proper output. EDIT3: Anybody who can tell me how to figure out this query..???

Original source