Need Help, Please! Sorting Problem...

Anon

Hi there,

I am trying to sort a table using three separate fields, which are INT fields. My table looks something like this.

word primary_index second_index tert_index
----+--------------+-------------+-------

char| int | int | int

I want the table to be sorted from least to greatest, no matter which index the number lies in. For example, if one word's primary index is 1, and another's secondary index is 2, I want them to be in that order.

However, I have not been able to come up with an ORDER BY clause that does this.

Does anybody at all have any suggestions? Your help is greatly appreciated.

-Bradley "Clueless" Morgan

Anon

What if you had

word1:
primary_index 1
second_index 2
tert_index 3

word2:
primary_index 2
second_index 1
tert_index 3

which one should come first? word1 or word2? If word1, then just do

ORDER BY primary_index, second_index, tert_index

Diego

Anon

Well...perhaps I should elaborate. I may even be on the complete wrong track to finding a solution...

The table consists of a list of words and their associated properties (i.e. english translation, spanish translation, syllables, ect.)

These words can belong to certain groups such as person, place, thing. But, a word can belong to more than one group. Thats where the indices come into play.

Let say I had the word chicken which belonged to the groups birds and food.

The primary group is birds, and this word appears third on the list, so it's primary index is 3. The secondary group is food, and chicken appears 9th in the list, so its secondary index is 9.

When I want to display the food table, chicken should come ninth, but when I use the clause ORDER BY primary_index, second_index, tert_index...the words don't come out in the right order.

It seems that all of the primary indices are sorted and then the secondary indices and then the tertiary which is completely out of whack.

It seems almost like I need to combine these columns at the time of sorting and then order the list by that column.

Is that any clearer? Sorry for the length. Thanks again!

-Bradley

Anon

Well, if you want to display the food table, you order by secondary index:

ORDER BY second_index

if you want to display the birds, you order by primary index:

ORDER BY primary_index

So you'll have if statements:

$sql = "SELECT * FROM tbl";
if ($category == "birds") {
$orderClause = " ORDER BY primary_index";
} else if ($category == "food") {
$orderClause = " ORDER BY second_index";
}

$sql .= $orderClause;

Hope this helps...

Diego