ORDER BY Multiple Columns

[tomyknoker]

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

Code

ORDER BY lastName ASC, State 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...

[codeexploiter]

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

----------------------------------
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).

SELECT * FROM `emp` ORDER BY empname ASC , salary DESC

The output would be something like the following

----------------------------------
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.

[thetestingsite]

Shouldn't

empsal DESC

be:

salary DESC

At least according to your example table you posted.

[codeexploiter]

Shouldn't

empsal DESC

be:

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

[ayswarya]

Did you try this way:

ORDER BY lastName, State ASC


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

[codeexploiter]

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).