MySQL select columns based on their contents...

English Programming/Scripting
1

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

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

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

4

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

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

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

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.

8

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/