Keep Server Online
If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.
or
A donation makes a contribution towards the costs, the time and effort that's going in this site and building.
Thank You! Steffen
Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
| |
|
 Topic: SQL sort by two columns |
|
| Author |
|
James Blond
Moderator
Joined: 19 Jan 2006
Posts: 7371
Location: Germany, Next to Hamburg
|
 Posted: Wed 30 Apr '08 15:18 Post subject: SQL sort by two columns |
 |
|
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: 611
Location: Milford, MA, USA
|
| Posted: Thu 01 May '08 1:57 Post subject: |
|
|
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: 7371
Location: Germany, Next to Hamburg
|
| Posted: Thu 01 May '08 8:46 Post subject: |
|
|
| 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 |
|
|
|
|
|
|
