Ordering Contents of a Field Alphabetically
Posted: Sat Feb 28, 2009 4:43 am
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:
Is there something I can add to achieve what I need?
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)