logo
Apache Lounge
Webmasters

 

About Forum Index Downloads Search Register Log in RSS X


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

Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
Post new topic   Forum Index -> Coding & Scripting Corner View previous topic :: View next topic
Reply to topic   Topic: SQL sort by two columns
Author
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
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: 611
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: 7288
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


Reply to topic   Topic: SQL sort by two columns View previous topic :: View next topic
Post new topic   Forum Index -> Coding & Scripting Corner