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
sory by
Moderator: General Moderators
Re: sory by
The Simplest way would be to exclude the rows that don't have a rank yet.
Code: Select all
.. WHERE `mycol` != 0-
rvijay_eee
- Forum Newbie
- Posts: 4
- Joined: Tue May 13, 2008 4:16 am
Re: sory by
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
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).
[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).
There are 10 types of people in this world, those who understand binary and those who don't