SORT BY with condition?

[V@no.]

Hi!
is there a way to do sorting depence of some condtion directly in the MySQL query?
for example, I have this query:
SELECT cat_id, cat_name, cat_date, cat_parent
FROM categories
ORDER BY cat_name ASC
but I'd like change sorting if cat_parent = 23 for example.
so, is there a way use something like:
SELECT cat_id, cat_name, cat_date
FROM categories
ORDER BY ((IF cat_parent = 23) THEN cat_date ELSE cat_name) ASC
I dont know MySQL at all, so this is just an example of condition idea that I'd like to use.

hope that's possible to do.
thx.

[raf]

I don't think it is possible. But you can have the condition in PHP or ASP or ... and add the order by clause to the sql-statement, depending on the value
like

$sqlquery = "SELECT_cat_id,_cat_name,_cat_date FROM_categories"
if $cat_parent == 23 {
$order = " ORDER_BY_cat_date ASC" ;
}
else {
$order = " ORDER_BY_cat_name ASC" ;
}
$sqlquery = $sqlquery + $order

[V@no.]

yes, that what I have now...unfortunetly that does GLOBAL sorting, not specific category...:(
but thx for the reply ;)

[raf]

unfortunetly that does GLOBAL sorting, not specific category
What do you mean?

[V@no.]

well, ok, lets say I have 10 categories,
and 4 sub-categories inside category number 7.
in the database all those categoryes has "parent_id" set to 0, and only the sub_categories has "perent_id" set to 7.
so, if u do the way u showed, it will sort ALL categories including sub-categories by whatever was after PHP condition. But I'd like to sort everything by name and only those 4 sub-categories by date...

ah, I think I forgot to mention, that after this query executed in the result will be list of all categories + sub-categories.

[raf]

I see. (i think)
you could use a union. You can think of it like this : the db first makes two selects (one for parent_id = 7 ordered by date and one for all other parent_id's ordered y name) and then creates a union to get these two views (the ordered records from the two selects) into one view (the recordset you need) which can then be ordered by another variable. I think that with equal paren_id's the records will stay in the same order (not sure, so maybe you'll have to experiment a bit with that.
$sqlquery_=_"(SELECT cat_id, cat_name, cat_date FROM categories WHERE parent_id = 7 ORDER BY cat_date ASC)UNION(SELECT cat_id, cat_name, cat_date FROM categories WHERE parent_id = 0 ORDER BY cat_name ASC) ORDER BY cat_id";
UNION is available since MySQL version 4.0
This tells you how to work around it on 3.x
http://www.nstep.net/~mpbailey/programming/tutorials.union.php
but i don't know if this would work for the stuff you need. I don't think so, since that form only builds one view so you can only have only one way to order it ...

I don't know your db-design, but it's probably easier to modify your db-design a bot to run this kind a querys more easy (like adding an extra date-variale that is only filed in for categorie 7.