Page 1 of 1

sory by

Posted: Thu May 15, 2008 12:54 pm
by Foolsworld
this is my problem:

Use dreamweaver, database is mysql. I want to sort rankings of tennis players. The problem is that the default value in mysql for smallint is 0. (So when I sort by asc. it ranks 0 first but should be last since these players have no ranking yet. How to overcome this problem?

Thanks for the help

Re: sory by

Posted: Thu May 15, 2008 1:04 pm
by Zoxive
The Simplest way would be to exclude the rows that don't have a rank yet.

Code: Select all

.. WHERE `mycol` != 0

Re: sory by

Posted: Fri May 16, 2008 12:04 am
by rvijay_eee
You can use an if condition in the order by clause, give the largest number in the order by clause

Code: Select all

SELECT *
FROM `events`
WHERE 1
ORDER BY if( `parent_id` =0, 200000 ,  `parent_id` ) asc
 

Re: sory by

Posted: Fri May 16, 2008 2:06 am
by VladSun
I would rewrite rvijay_eee's query as follows:
[sql] SELECT       *, (`parent_id` != 0) AS `has_rank`FROM       `events`ORDER BY       `has_rank` DESC, `parent_id` ASC [/sql]

This way you have an additional field "has_rank" (no PHP logic required) and more important - no bondary cases (like "200000" in rvijay_eee's query).