SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT DISTINCT into multiple aliases

    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!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    DISTINCT(wo.wo) !!!!

    (awesomest post this week, hands down)

    could you please do a SHOW CREATE TABLE for this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, sure.
    Schema was created only for testing purposes.
    WO bigint(20) UNSIGNED
    ITEM varchar(100)
    DATE1 date
    DATE2 date
    QTY int(11)

    Quote Originally Posted by r937 View Post
    DISTINCT(wo.wo) !!!!

    (awesomest post this week, hands down)

    could you please do a SHOW CREATE TABLE for this table

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    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.

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im sry, I put the wrong column name, I need distinct(wo.ITEM), that is not a PK

    Quote Originally Posted by r937 View Post
    DISTINCT(wo.wo) !!!!

    (awesomest post this week, hands down)

    could you please do a SHOW CREATE TABLE for this table

  7. #7
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx a lot!

    I m trying to do this using zend framework API.
    Code:
    $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();

    Quote Originally Posted by Dr John View Post
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face

    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.

    Quote Originally Posted by r937 View Post
    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???

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Europe
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    // 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; 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •