Ordering Contents of a Field Alphabetically

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Ordering Contents of a Field Alphabetically

Post by millsy007 »

Hi
I have some results that I would like to order but I am not sure how this is done.I have a query to bring out a list of passengers on a journey. To make it clearer for the user I would like to be able to order the way in which
the contents of a particular field are shown. Currently the passengers are shown in the passenger_name field in a seemingly random way:

departs spaces passengers
2009-02-27 10:00:00 4 lee | rob | scott | paul
2009-02-27 10:25:00 4 lee | paul | scott |rob
2009-02-27 10:55:00 7 lee

For clarity I would like to order the contents of this field alphabetically:

departs spaces passengers
2009-02-27 10:00:00 4 lee | paul | rob | scott
2009-02-27 10:25:00 4 lee | paul | rob | scott
2009-02-27 10:55:00 7 lee

I know there is an order by clause but I think that this just orders all the results vertically as opposed to within the actual field?

This is my sql:

Code: Select all

(SELECT   tb2.journey_dttm as departure, ('8' - tb2.occupancy) AS spaces,
(SELECT IF(GROUP_CONCAT(passenger_name SEPARATOR '|') IS NULL, '', GROUP_CONCAT(passenger_name SEPARATOR '|'))  
FROM passengers WHERE journey_id = tb2.id) AS passengers FROM shuttle AS tb1 LEFT JOIN journey AS tb2 ON 
( tb1.id = tb2.shuttle_id ) LEFT JOIN trip_route AS tb3 ON ( tb2.route_id = tb3.id ) 
WHERE tb1.depart_dttm = '$depart_dttm' ORDER BY tb2.id ASC, tb2.route_id ASC)
Is there something I can add to achieve what I need?
Post Reply