Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    avg or something

    Data
    Code:
    f1          level  taxon
    
    kingdom     1        a   
    phylum      2        a
    class       3        a      
      
    kingdom     1        b   
    phylum      2        b
    subphylum   3        b
    class       4        b    
    ....
    species     10       b
    
    ....                 c
    Taxon 'a' goes only to class as this is leveling for ceratin class only !
    Sure you would say, why you dont have hirearchical ..., Coz data are from diff. sources --> levels missing (like some souces don't have subphylum in path etc), various other reasons ...

    So how to get this

    Code:
    kingdom   
    phylum
    subphylum
    class  
    ...
    species
    from upper data in right order, as within 'taxon' they are ordered right by 'level' column ?
    Or this is lost case ?
    Last edited by BubikolRamios; 02-17-2012 at 06:09 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    easy.

    Change column f1 from VARCHAR to ENUM.

    Code:
    CREATE TABLE whatever (
        f1 ENUM('kingdom','phylum','subphylum','class',....,'species'),
        level INT,
        taxpn VARCHAR(255) 
    );
    Then just ORDER BY f1, taxon. (Or taxon, f1) (Or whatever you need.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    If you don't want to change your main table, it's almost as easy.

    Create another table:

    Code:
    CREATE TABLE ordering( f1 VARCHAR(30) PRIMARY KEY, inorder int );
    
    INSERT INTO ordering( f1, inorder )
    VALUES( 'kingdom', 1 ), ('phylum', 2 ), ('subphylum', 3), ....('class', 10);
    
    SELECT maintable.*
    FROM maintable, ordering
    WHERE maintable.f1 = ordering.f1
    ORDER BY ordering.inorder, maintable.taxon;
    But the first way will be more efficient.
    Last edited by Old Pedant; 02-17-2012 at 07:50 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Best not to use ENUM as that makes future modifications difficult - particularly if you need to remove a value from the middle of the list.

    A better way is to use a numeric field that is defined as a foreign key and allocate the corresponding values in a separate table.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Ummm...I doubt that the science of taxonomy is going to be adding too many levels in the near future.

    The ordering of kingdom, phylum, etc., has been around for a LOOOOONNNNNG time now, and if anybody made changes to it, it would disrupt a whole lot more than this database.

    And I disagree about using the numeric field as a foreign key. His "f1" field is just fine for that purpose. It makes no sense to add another field that duplicates that (as a number). The primary key values that I showed in my ordering table--that is, the names of the taxonomic classifications--are not all that long and they form the most natural possible primary key. And thus make a great foreign key in his main table. Granted, he *could* remove the names from the main table and REPLACE them with a numeric foreign key, but all he will be saving is a few bytes per record. I don't understand this predilection so many people have with always using an auto_incremeent field as the primary key. If there is no other reasonable primary key, okay. But when there is a wonderful natural primary key like this one (taxonomic level), you should use it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    Ummm...I doubt that the science of taxonomy is going to be adding too many levels in the near future.
    You are probably right about that but generally an ENUM field is a bad idea.

    Another reason for not using them is that you then have to build your own translation of the numeric values that it uses into the text representations that they represent as the text representations cannot be retrieved from the database directly. That reason applies to this situation even if the more common reason for not using them does not.

    Quote Originally Posted by Old Pedant View Post
    And I disagree about using the numeric field as a foreign key. His "f1" field is just fine for that purpose. It makes no sense to add another field that duplicates that (as a number).
    I agree with you about overuse of auto-increment when there's a perfectly valid field to use. Just that ENUM basically replaces the field with a number and IF you are going to use a number then having it as a foreign key would be better than having it as an ENUM as then you can look up the other table to get the text it represents.

    In this instance keeping the names in the field and having that as a foreign key to another table that just contains the one field listing the valid names is probably the best option for allowing the database to ensure that the field only contains valid values.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    ust that ENUM basically replaces the field with a number and IF you are going to use a number then having it as a foreign key would be better than having it as an ENUM as then you can look up the other table to get the text it represents.
    Except that with MySQL, when you SELECT from an ENUM field you get the text form, not the numeric form.

    But never mind. I see where you were coming from and apologize for misreading you. Under normal circumstances what you proposed is almost surely the better choice.

    I think that for this case--taxonomy--either solution works, but that's going to be the exceptional case, indeed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    I think that for this case--taxonomy--either solution works, but that's going to be the exceptional case, indeed.
    I agree. My first post was a bit ambiguous as this is one of the very few instances where using an enum in mysql wouldn't hurt anything. I should have made it clearer that this is actually a rare exception and that my comment was intended to apply more generally.

    And just to add that there is yet another benefit to be gained by doing it the other way most of the time in that using enum you still can't retrieve a list of all of the allowed values and so would need to hard code them in whatever script you are using to collect the value for that field. Of course since the list will never never change in this particular instance that isn't going to present any difficulty in this exceptional case either.
    Last edited by felgall; 02-18-2012 at 12:22 AM.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Well, just to be anal retentive about it...
    Code:
    mysql> select column_type from information_schema.columns 
         > where table_schema='test' 
         > and table_name='forFelgall' 
         > and column_name='fruit';
    +-------------------------------------+
    | column_type                         |
    +-------------------------------------+
    | enum('apple','banana','canteloupe') |
    +-------------------------------------+
    <grin style="s.e."/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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