How to sort in MySQL?

misunderstood · #1 30-Sep-2003, 17:24

Now for any easy question

Can I select from a table and sort the data from a column and then sort again from another column?

An example to clear the mud (waffle)

PHP Code:


Select * from table sort by column a

Now the data in column a is sorted into data X and data Y
Can I now sort the data X and data Y by column b so that data X is in order and data Y is in order?

I tried

PHP Code:


select * from table GROUP BY column a SORT BY column b

But that didnt work although I may be misunderstanding the commands.

JdS · #2 01-Oct-2003, 05:59

You can sort multi columns easily in a table / SQL Select statement using MySQL's ORDER BY clause.

So, when you want to sort or 'order by' multi columns, you just list the coloumn names in the order of their precedence (left > right), each separated by a comma.

Let's say you have a table like this:

Code:

TABLE NAME: `members`
=====================================
  id | lastname   | firstname  | pts
-----+------------+------------+-----
  1  | Smith      | Kate       | 105
  2  | Smith      | Will       |  99
  3  | Jones      | Tommy L    | 100
  4  | Presley    | Lisa       | 102
  5  | Presley    | Elvis      |  50

If you run:

PHP Code:


<?php
$sql = 'SELECT `lastname`, `firstname`
       FROM `members`
       ORDER BY `lastname`';
?>

You'd get:

Code:

lastname   | firstname
-----------+------------
 Jones     | Tommy L
 Presley   | Lisa
 Presley   | Elvis
 Smith     | Kate
 Smith     | Will

But, notice that the results are less than perfect especially when you consider the 'Presleys'. To sort / ORDER BY firstnames too, you could add the firstname column to the ORDER BY clause, like this:

PHP Code:


<?php
$sql = 'SELECT `lastname`, `firstname`
       FROM `members`
       ORDER BY `lastname`, `firstname`';
?>

Now the results are much better; they would look something like this:

Code:

lastname   | firstname
-----------+------------
 Jones     | Tommy L
 Presley   | Elvis
 Presley   | Lisa
 Smith     | Kate
 Smith     | Will

If you ever wanted to sort the lastname ASC (i.e. A through Z) and firstname DESC (i.e. Z through A)... Try:

PHP Code:


<?php
$sql = 'SELECT `lastname`, `firstname`
       FROM `members`
       ORDER BY `lastname` ASC, `firstname` DESC';
?>

Which would result in something like this instead:

Code:

lastname   | firstname
-----------+------------
 Jones     | Tommy L
 Presley   | Lisa
 Presley   | Elvis
 Smith     | Will
 Smith     | Kate

misunderstood · #3 01-Oct-2003, 07:50

Ah I see why when I tried that one it didnt work for me! I missed the ' ' around the extra column.
Now a further extension to this. Part of my information (numerical) is pulled from a table and uses another php document for the awarding of points.
ie 2 in my goals column and 1 in my assist column, points column is set at 0. Using my points.php this awards 2 points for each goal and 1 point for each assist. Total = 5 points

PHP Code:


$points=points($hid,$se);

$points is included in the row recall.
Can I now get all the rows to sort by points?
I suspect that I can by using an array but I am unsure of how i go about it.
There again I am probably barking up the wrong tree! :0

JdS · #4 01-Oct-2003, 09:01

Try explaining that again, couldn't quite work it out in my head!

It might be wiser to start a whole new thread if this 2nd question is unrelated to the first post / issue.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 13:54

How to sort in MySQL?

Sorting multi columns in a MySQL table with ORDER BY