Mikle
January 20, 2010, 10:55pm
1
HI,
I know I can find much of help here.
I was trying to find my answer in google, but failed.
How do I select
DISTINCT(wo.wo)
into few aliases like ‘name’ and 'label;. Since I cannot do:
DISTINCT(wo.wo) as name, DISTINCT(wo.wo) as label
I couldnt not find any good article about multiple aliases issue (.
Thanx alot!
Why do you want two identical columns? Shouldn’t it be your program code, not your database query, that handles outputting them in two places if necessary?
r937
January 20, 2010, 11:18pm
3
DISTINCT(wo.wo) !!!
(awesomest post this week, hands down)
could you please do a SHOW CREATE TABLE for this table
Mikle
January 20, 2010, 11:26pm
4
Yes, sure.
Schema was created only for testing purposes.
WO bigint(20) UNSIGNED
ITEM varchar(100)
DATE1 date
DATE2 date
QTY int(11)
Mikle
January 20, 2010, 11:28pm
5
Because I dont want to do additional extra loop in my code.
Mikle
January 20, 2010, 11:30pm
6
Im sry, I put the wrong column name, I need distinct(wo.ITEM), that is not a PK
Dr_John
January 20, 2010, 11:48pm
7
SELECT DISTINCT wo.item as firstOne, wo.item as secondOne FROM tablename;
Why you didn’t just try this I don’t know, but it does work, as I’m looking at some output just now.
Mikle
January 20, 2010, 11:54pm
8
Thanx a lot!
I m trying to do this using zend framework API.
$select = $db->select();
$select->from(array('wo' => 'WO'), array(
'name' => new Zend_Db_Expr('DISTINCT(wo.ITEM)'),
)
);
$select->having('LENGTH(TRIM(name))>0');
$select->order('name ASC');
$stmt = $db->query($select);
$rows = $stmt->fetchAll();
Dr_John:
SELECT DISTINCT wo.item as firstOne, wo.item as secondOne FROM tablename;
Why you didn’t just try this I don’t know, but it does work, as I’m looking at some output just now.
r937
January 21, 2010, 12:57am
9
please, please, please don’t write DISTINCT(wo.item)
DISTINCT is ~not~ a function
also, where did “name” come from? what are the two dates for?
and what does “aliases like ‘name’ and ‘label’” mean???
Mikle
January 21, 2010, 1:12am
10
Yes, thanx a lot r937. I thought DISTINCT was a function. And when I create select object in zend I used name=>DISTINCT(wo.ITEM) in my columns,
and I figured out now that they have a special $select->disticnt() method to use.
These two dates are Work Order(WO) properties I dunno what for yet exactly. Just used a simple names for them.
r937:
please, please, please don’t write DISTINCT(wo.item)
DISTINCT is ~not~ a function
also, where did “name” come from? what are the two dates for?
and what does “aliases like ‘name’ and ‘label’” mean???
Mikle
January 21, 2010, 1:17am
11
Aliases name and label are used for Dojo to feed the dojo ItemFileWriteStore that later will be a source for dojo combo box dropdown. Basically its a dropdown element programming.
// server side
$db = Zend_Registry::get('db');
$select = $db->select()->distinct();
$select->from(array('wo' => 'WO'), array(
'name' = 'wo.ITEM',
'label' => 'wo.ITEM',
)
);
$select->having('LENGTH(TRIM(name))>0');
$select->order('name ASC');
$stmt = $db->query($select);
$rows = $stmt->fetchAll();
$dojoData = new Zend_Dojo_Data('name', $rows, 'label');
print $dojoData;
exit;