logo
Apache Lounge
Webmasters

 


About

Forum Index Downloads Search Register Log in  RSS Apache Lounge
 



Keep Server Online

If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.

or

Bitcoin

A donation makes a contribution towards the costs, the time and effort that's going in this site and building.

Thank You! Steffen

Apache Lounge is not sponsored.

Your donations will help to keep this site alive and well, and continuing building binaries.



SQL sort by two columns

 
Post new topic   Reply to topic    Apache Forum Index -> Coding & Scripting Corner



View previous topic :: View next topic  
Author Message
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 6255
Location: Germany, Next to Hamburg

PostPosted: Wed 30 Apr '08 15:18    Post subject: SQL sort by two columns Reply with quote

I want to sort first by zeit and than by treffer. But it only sorts by zeit

Code:

SELECT * FROM `highscore1` ORDER BY `zeit` ASC,`treffer` DESC LIMIT 0, 10



now the result is:
Code:

2     test2     27.00     9
3    ttest3    27.00    50
4    test4    27.00    3
1    test1    45.45    8


but it should be

Code:

3    ttest3    27.00    50
2     test2     27.00     9
4    test4    27.00    3
1    test1    45.45    8


Any idea how to modify the select?

----
edit

my table
Code:

CREATE TABLE `highscore1` (
`id` INT( 8 ) NOT NULL AUTO_INCREMENT ,
`spielername` VARCHAR( 255 ) NOT NULL ,
`zeit` VARCHAR( 5 ) DEFAULT '0' NOT NULL ,
`treffer` VARCHAR( 3 ) DEFAULT '0' NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM ;
Back to top
tdonovan
Moderator


Joined: 17 Dec 2005
Posts: 610
Location: Milford, MA, USA

PostPosted: Thu 01 May '08 1:57    Post subject: Reply with quote

The problem seems to be that zeit and treffer are strings (VARCHARs), not numbers - so they do not get compared as numbers.

As a string, "9" is greater than "50" - but as numbers, 50 is greater than 9.

If you CAST them to numbers they will be compared correctly:
Code:
SELECT * FROM `highscore1` ORDER BY CAST(`zeit` AS DECIMAL) ASC, CAST(`treffer` AS DECIMAL) DESC LIMIT 0, 10

It would probably be better if zeit and treffer were defined as numbers in the table, unless they can sometimes contain strings which are not numeric. Maybe something like this:
Code:
CREATE TABLE `highscore1` (
`id` INT(8) NOT NULL AUTO_INCREMENT ,
`spielername` VARCHAR( 255 ) NOT NULL ,
`zeit` DECIMAL(6,2) DEFAULT 0 NOT NULL ,
`treffer` INT(8) DEFAULT 0 NOT NULL ,
PRIMARY KEY ( `id` )) TYPE = MYISAM ;


-tom-
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 6255
Location: Germany, Next to Hamburg

PostPosted: Thu 01 May '08 8:46    Post subject: Reply with quote

Thanks! That works. My fault was that I thought it would be as in PHP. There I don't have to which type it is. But it makes sence that mysql can only sort numbers in the way I want and sort strings by the length.
Back to top


Post new topic   Reply to topic    Apache Forum Index -> Coding & Scripting Corner
Page 1 of 1