sory by

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Foolsworld
Forum Newbie
Posts: 3
Joined: Sat May 10, 2008 10:56 pm

sory by

Post 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
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: sory by

Post by Zoxive »

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

Post 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
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: sory by

Post 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).
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply