Building a select distinct on mysql ( zend_db)

Question

I HAVE THE FOLLOWING TABLE, I WOULD LIKE TO GET to do a select distinct on the the column [code], i don't need to get the "A" three times.

[ ID ]   [ CODE ]     [ LIBELLE ]
1         A        LIBELLE1  
2         B        LIBELLE2
3         C        LIBELLE3
4         A        LIBELLE4  
5         A        LIBELLE5
6         D        LIBELLE6

I want the result as following

[ ID ] [ CODE ] [ LIBELLE ]
1         A        LIBELLE1  
2         B        LIBELLE2
3         C        LIBELLE3
6         D        LIBELLE6

i just cant understand your format? – Pramendra Gupta Sep 22 '10 at 08:55 — Pramendra Gupta, Sep 22 '10 at 08:55

score 3 · Accepted Answer · answered Sep 22 '10 at 08:57

3

Just add

group by code 
ORDER BY code ASC

at end of your sql query

example

select * from table
group by code 
ORDER BY code ASC

answered Sep 22 '10 at 08:57

Pramendra Gupta

14,667
4
33
34

score 1 · Answer 2 · answered Sep 22 '10 at 08:55

1

 SELECT Min(Id) Id, Code, MIN(Libelle) Libelle
 from table
 group by code

answered Sep 22 '10 at 08:55

Michael Pakhantsov

24,855
6
60
59

am gonna test it, it seems very clever this method – Mamadou Sep 22 '10 at 08:58

score 1 · Answer 3 · answered Sep 22 '10 at 09:11

If you are looking for Zend_Db_Select usage, here it is

$db->select()->from('table', array(
    'Id' => new Zend_Db_Expr('Min(ID)'),
    'Code' => 'CODE',
    'Libelle' => new Zend_Db_Expr('Min(LIBELLE)')
))->group('CODE');

$db should be your Zend_Db_Adapter.

Building a select distinct on mysql ( zend_db)

3 Answers3