[SOLVED] Ordering Contents of a Field Alphabetically

[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:

 

(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?

[corbin]

I would normalize the schema a bit.

 

 

Something like:

 

 

CREATE TABLE flights (

    flight_id int NOT NULL AUTO_INCREMENTING PRIMARY KEY,

    departure_time datetime NOT NULL,

    spaces smallint

);

 

CREATE TABLE flight_passengers (

    flight_id int NOT NULL,

    passenger_name VARCHAR(255)

);

 

 

 

Then it would be easier to extract data.

 

(You could go one step farther and have a passengers table with IDs mapped to passengers then map flight IDs to passenger IDs.)

 

 

 

I can try to help you do it your way though, if you want to stick with what you have.

[fenway]

group concat can take an order by!

[corbin]

Really?  Wow....  Never knew that.

 

Edit:  Oh, I just realized that I think I entirely misunderstood the OP's table layout....

 

So pretty much just ignore my posts in this thread ;p.

[millsy007]

Hi thanks for the posts, this is pretty much the last issue I have with my program so would preferably not like to change too much. But it sounds like there isnt an 'easy' way to order the values in the passenger_name field?

[fenway]

Hi thanks for the posts, this is pretty much the last issue I have with my program so would preferably not like to change too much. But it sounds like there isnt an 'easy' way to order the values in the passenger_name field?

Did you try using ORDER BY inside the GROUP_CONCAT() ???

[millsy007]

I tried:

 

(SELECT tb1.depart_dttm as departure,  tb3.route_desc as route, ('7' - 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 ORDER BY passenger_name) 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 DATE(tb1.depart_dttm) = '2009-01-30' ORDER BY tb2.id ASC, tb2.route_id ASC)

 

[Mchl]

How about

GROUP_CONCAT(passenger_name ORDER BY passenger_name SEPARATOR '|')

[millsy007]

Yes that does it, I was adding the order by to the wrong part, cheers