How can get unique values from data table using dql?
- by piemesons
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..???
