Results 1 to 6 of 6

Thread: ORDER BY Multiple Columns

  1. #1
    Join Date
    Apr 2006
    Posts
    584
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default ORDER BY Multiple Columns

    I am trying to order by multiple columns, here is my example and what I want to output

    Code
    PHP Code:
    ORDER BY lastName ASCState ASC 
    Output
    James Anna, Alabama
    Ben Bloggs, Alabama
    Chris Cats, Alabama
    Mark Aims, Delaware
    Hopefully you can see what I am trying to achieve, but I don't get there all it does is sort the result by lastName and ignores the states...

  2. #2
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,626
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    The basic idea of using multiple fields in ORDER BY clause is something like the following.

    Consider you have a table with the following structure and data
    Code:
    ----------------------------------
    empid | empname         | salary |
    ----------------------------------
    100   | Johnson         | 10000  |
    ----------------------------------
    200   | Adam            | 12000  |
    ----------------------------------
    300   | Mike            | 11000  |
    ----------------------------------
    400   | Johnson         | 17000  |
    ----------------------------------
    500   | Tomyknoker      | 10000  |
    ----------------------------------
    You are executing the following SQL statement on the above mentioned table (name of the table is emp).

    Code:
    SELECT * FROM `emp` ORDER BY empname ASC , salary DESC
    The output would be something like the following

    Code:
    ----------------------------------
    empid | empname         | salary |
    ----------------------------------
    200    | Adam              | 12000 |
    ----------------------------------
    400    | Johnson           | 17000 |
    ----------------------------------
    100    | Johnson           | 10000 |
    ----------------------------------
    300    | Mike                | 11000 |
    ----------------------------------
    500    | Tomyknoker      | 10000  |
    ----------------------------------
    Now we've performed a first level sorting based on empname and two persons with the same name exist in the above table on thsoe two records only it will perform a second level sorting which is based on empsal field in a descending manner. So in this case the Johnson whose empid is 400 and salary is 17000 came before the other Johnson whose record kept in the original table before the person with an empid 400.

    Hope this is clear now.
    Last edited by codeexploiter; 04-18-2007 at 05:29 AM.

  3. #3
    Join Date
    Sep 2006
    Location
    St. George, UT
    Posts
    2,769
    Thanks
    3
    Thanked 157 Times in 155 Posts

    Default

    Shouldn't

    Code:
    empsal DESC
    be:

    Code:
    salary DESC
    At least according to your example table you posted.
    "Computer games don't affect kids; I mean if Pac-Man affected us as kids, we'd all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music." - Kristian Wilson, Nintendo, Inc, 1989
    TheUnlimitedHost | The Testing Site | Southern Utah Web Hosting and Design

  4. #4
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,626
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    Quote Originally Posted by thetestingsite View Post
    Shouldn't

    Code:
    empsal DESC
    be:

    Code:
    salary DESC
    At least according to your example table you posted.
    Yes it was a mistake from my part corrected. Thanks for the info man

  5. #5
    Join Date
    Mar 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Try this

    Did you try this way:

    ORDER BY lastName, State ASC


    *********************************************************

  6. #6
    Join Date
    Sep 2005
    Location
    India
    Posts
    1,626
    Thanks
    6
    Thanked 107 Times in 107 Posts

    Default

    Quote Originally Posted by ayswarya View Post
    Did you try this way:

    ORDER BY lastName, State ASC


    *********************************************************
    Actually the default sorting order is ascending you specify it or you don't specify it will perform a ascending order sorting. Only in case of a descending order sorting you need to specify DESC.

    In the above code first it will perform an ascending order sorting on the basis of lastName and if there is any similar items in lastName and then only it will perform another ascending order sorting on the basis of State field only on those similar records (not all records).

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •