Results 1 to 8 of 8

Thread: MySQL select columns based on their contents...

  1. #1

    Default MySQL select columns based on their contents...

    Okay, so what I have is basically a large binary table, with n rows and 48 columns.
    What I need to do is select an arbitrary number of rows, and then from within that selection, return only the columns that do NOT contain ANY 'ones'. (or the columns sorted in order of which contains the fewest total 'ones'. Either works.)
    There's a long way to do this with a VERY lengthy SQL statement that I don't feel like writing, so I imagine there must be a more efficient way...
    any thoughts?

  2. #2
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: MySQL select columns based on their contents...

    A long SQL statement may be unavoidable. Problem is those columns are addressed by name and not by index. SQL and RDBs are not suitable for all problems.

  3. #3

    Default Re: MySQL select columns based on their contents...

    Hmm... perhaps I can do something better by processing the array in PHP...

  4. #4
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: MySQL select columns based on their contents...

    Yeah read in the data in a programming language where these things are easier to do.

    But from another point of view, the fact that you have 48 columns of features means that your data is not normalised for a RDBMS. If normalised you would have a thing table that looks like this:

    id,thing

    a feature table that looks like this:

    id,feature

    and a correlation table that looks like this:

    id,thingid,featureid

    if feature is present in thing. Then the SQL is quite doable.

  5. #5
    Dave Parkes NNTP User

    Default Re: MySQL select columns based on their contents...

    Not really that bad

    select id, (field1 + field2.... + field48) as ftotal order by ftotal asc
    limit startrow, finishrow

    Cheers Dave


    --
    Dave Parkes [NSCS]
    Occasionally resident at http://support-forums.novell.com/

  6. #6
    Dave Parkes NNTP User

    Default Re: MySQL select columns based on their contents...

    Arrgh, columns not rows !, sorry about that. I think you'd do it with
    embedded selects so the (f1 + f2...) not becomes (select sum(f1) from
    dbname limit s,f + etc)

    Cheers Dave


    --
    Dave Parkes [NSCS]
    Occasionally resident at http://support-forums.novell.com/

  7. #7
    Join Date
    Jun 2008
    Location
    New Zealand
    Posts
    78

    Default Re: MySQL select columns based on their contents...

    If I understand you correctly, you want to return a list of columns from your result set which contain only zero?

    The only way I could imagine doing this is with a stored procedure which creates a temporary table or view, and return that.

    That would be about the same effort as doing so in PHP or Perl.

    As Ken mentioned, that fact that the table is not normalised has made what should be quite trivial into something not so.

    Best of luck.
    BZFlag: good clean fun. Sorta.

  8. #8
    Dave Parkes NNTP User

    Default Re: MySQL select columns based on their contents...

    To GransRemedy

    >As Ken mentioned, that fact that the table is not normalised has made
    >what should be quite trivial into something not so.


    Yep, definitely be worth the time to restructure this one table into the
    three separate tables that Ken suggested.

    Cheers Dave


    --
    Dave Parkes [NSCS]
    Occasionally resident at http://support-forums.novell.com/

Posting Permissions

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