sorting on two different fields?

[Overgrow]

My duBois book is at home so I figured I'd ask here to see if anyone has a quick answer. Is there a way to sort on two different fields AND arrange them differently with DESC,ASC?

For example... take this query (please)

SELECT * FROM table WHERE category='$id' ORDER BY field1,field2 DESC

That's great except that I want field1 sorted with DESC and then field2 sorted with ASC. This does not work:

SELECT * FROM table WHERE category='$id' ORDER BY field1,field2 DESC,ASC

Is there any way to do this without reading the results into an array and re-sorting it yourself?

[CyberFuture]

Try this:

SELECT * FROM table WHERE category='$id' ORDER BY field1 DESC,field2 ASC

[Overgrow]

Damn, good try but no cigar...

SELECT * FROM table WHERE category='$id' ORDER BY field1 DESC,field2 ASC

returned the same results as

SELECT * FROM table WHERE category='$id' ORDER BY field1,field2 ASC

It ignored the DESC and sorted both with ASC...... any other ideas?

[freddydoesphp]

Out of curiosity I tested this, and I was able to get it to work as long as there are many to one type of relationship between category and userID like in my case:

mysql> select userID, category, UPC from ad WHERE Type = 999 ORDER BY userID DESC, category ASC LIMIT 10;



+---------------+----------------------+-------+
| userID        | category             | UPC   |
+---------------+----------------------+-------+
| zman            | Spam                 | 91602 |
| zman          | Cars & Trucks        | 06854 |
| zman          | Cars & Trucks        | 06854 |
| zman          | Cars & Trucks        | 06854 |
| zman          | Cars & Trucks        | 06854 |
| yman          | Drapes               | 06854 |
| yman          | Acorns               | 90046 |
| xman          | Spam                 | 90046 |
| xman          | Acorns               | 20007 |
| vman          | Acorns               | 60085 |
+---------------+----------------------+-------+ 

I hope that helps

[CyberFuture]

Strange

I went into Access2000 and used their QueryDesign feature and this is what I came up with:

SELECT * FROM table WHERE category='$id' ORDER BY field1 DESC, field2;

This query worked against my MySQL database via MyODBC in Access, but then again so did my original sugestion.

[Overgrow]

Sorry guys, you were right. That does indeed work.. I'm not sure what I screwed up the first time but it does work just as expected now.

thanks~!

[freddydoesphp]

Overgrow I was running into difficulty recreating the problem but I think it has to do with the fact once the first field is sorted and then you try to sort the second field its going to screw with the order.

[Overgrow]

I could've sworn it didn't work the first time (what was I smokin??) but it's working now-- sorting on 3 fields properly.

[freddydoesphp]

I don't know but "pass it over my friend!"