SELECT DISTINCT into multiple aliases

[Mikle]

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!

[Dan Grossman]

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]

DISTINCT(wo.wo) !!!!

(awesomest post this week, hands down)

could you please do a SHOW CREATE TABLE for this table

[Mikle]

Yes, sure.
Schema was created only for testing purposes.
WO bigint(20) UNSIGNED
ITEM varchar(100)
DATE1 date
DATE2 date
QTY int(11)

DISTINCT(wo.wo) !!!!

(awesomest post this week, hands down)

could you please do a SHOW CREATE TABLE for this table

[Mikle]

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?

Because I dont want to do additional extra loop in my code.

[Mikle]

Im sry, I put the wrong column name, I need distinct(wo.ITEM), that is not a PK

DISTINCT(wo.wo) !!!!

(awesomest post this week, hands down)

could you please do a SHOW CREATE TABLE for this table

[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.

[Mikle]

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();

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]

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]

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.

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]

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;