mySql question, not sure if its a Union or Join issue...

[SpockBert]

Hi I'm trying to do something quite simple but can't quite get my head round it and get the results to come out how I want them.

Say you have a load of cufflinks and you want to classify them for a database, some of the cufflinks might be simple, i.e all made out of white gold, some might be all yellow gold, others might be made out of both white and yellow so you have some overlapping classes.

I've got my db set up so "class1" is my primary and "class2" is my secondary. So if I've got a cufflink thats predominantly yellow gold but with a little bit of white I'll have "class1" set as "yg" and "class2" as "wg".

brief example...


I wanna see all cufflinks that feature "wg" so I've got something like..

SELECT * FROM myTable WHERE (class1 = "wg" OR class2 = "wg");

The results come out in an order like 1,2,3,4,5. What I really want is for the class1 results to take priority so the results should come out as 1,2,3,5,4.

I hope that makes sense, anybody know how you give one column priority over another in the same table?

Cheers

[fasco1]

try adding

ORDER BY class1;

this should sort them in order acheiving the same result you are looking for.

[SpockBert]

Thanks fasco but unfortunately its not that simple. My real table is quite complex but I wanted to keep it simple for examples but that one is too simple. This one better illustrates my problem:



What I would really like is to be able to select all rows that feature "wg" but the ones from "class1" to appear before "class2".

1,2,3,5,4,6

Simply doing an ORDER BY class1 would give me 4,1,2,3,5,6

Even doing a DESC doesn't help either.

If there was some way to order results by a column's <b>title</b> that would be fantastic, unfortunatelt I don't think that functionality exists

I can think of another way to do this where you have some sort of temp field called I dunno say "SUPERORDER". My script does its query looking for "wg" it its found in class1 it changes SUPERORDER for that row to A and if a match for "wg" is found in class2 SUPERORDER gets written as B. Then I'll order the entire selection by SUPERORDER!

That seems a ridiculous way of doing it but can't see an alternative right now!

[silentweed]

if i understand u correctly then the following should do it:

ORDER BY class1, class2;


heres a simple tutorial that may help:

http://www.tizag.com/sqlTutorial/sqlorderby.php