...

View Full Version : Ordering Contents of a Field Alphabetically



millsy007
03-01-2009, 12:33 PM
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 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)

bazz
03-01-2009, 02:04 PM
Before you do your query I think you should normalise the db. 1st normal form says that there should be one value per column. This means that you should have the passenger names stored in another table.

what if lee travels on more than one flight? I think you need a many-to-many table for passengers.

bazz

Old Pedant
03-01-2009, 07:22 PM
Ummm...bazz, I think the passenger_name values *ARE* in separate records in the DB.

Look at HOW he is getting the delimited list there:


GROUP_CONCAT(passenger_name SEPARATOR '|')

See the docs:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

Old Pedant
03-01-2009, 07:27 PM
Millsy: I think you just need to read the docs more carefully.

You have your ORDER BY for the GROUP_CONCAT in the wrong place.

I have never used GROUP_CONCAT, so this is strictly going by my reading of the docs, but I think what you want is this:


SELECT tb1.depart_dttm as departure,
tb3.route_desc as route,
(7 - tb2.occupancy) AS spaces,
( SELECT GROUP_CONCAT(passenger_name SEPARATOR '|' ORDER BY passenger_name)
FROM passengers
WHERE journey_id = tb2.id AND passenger_name IS NOT NULL
) 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;

See where the ORDER BY goes from GROUP_CONCAT??? That is according to the docs here:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

I also tried to simplify your query by just never GETTING any passenger_name values if they are NULL. I realize that this *could* result in your passengers value being NULL, but surely you can handle that in your reporting code as easily as you could handle ''??? Or just add another CONCAT('', GROUP_CONCAT(....)) if you must have the guaranteed ''.

Finally, why did you have '7' and then SUBTRACT from that???? You are just forcing MySQL to convert the string '7' to a number, for you.

Does this make sense??

As I said, I've never done this, so let me know if it works.

bazz
03-02-2009, 12:30 AM
Ummm...bazz, I think the passenger_name values *ARE* in separate records in the DB.


That's OK; I got the idea they were in the same column based on what he wrote, wihtout looking at the query:



Currently the passengers are shown in the passenger_name field in a seemingly random way:

and he has three columns which ~ as I write this, I can see means the pasengers are on the third col.


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




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




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?


Much better to have a many-to-many table. ;)


@millsy: if you post your create table statements we could help you with the normalisation process. I think you will run into difficulties very soon, with multi-value fields like that. eg. where would you store thr individual customer details if you would have to? And I can see a really clear reason for having to store them.


bazz

Old Pedant
03-02-2009, 04:26 AM
I'm sorry, Bazz, but I still think you are misdiagnosing what his tables look like.

I see them looking like this:


TABLE: shuttle
id INT : the primary key for this table
depart_dttm DATETIME

TABLE: journey
id INT : the primary key for this table
shuttle_id INT : foreign key to shuttle(id)
route_id INT : foreign key to trip_route(id)
occupancy INT : apparently never more than 7??

TABLE: trip_route
id INT : primary key for this table
route_desc VARCHAR

TABLE: passengers
passenger_name VARCHAR
journey_id INT : foreign key to journey(id)


(Surely lots of other fields not shown, but we can deduce all those from the query.)

So far as I can see, his normalization is fine.

Essentially, his "journey" table is indeed a many-to-many between "shuttle" and "trip_route". And then "passengers" is a one to many that connects multiple passengers to just ONE of the shuttle/trip_route combos as designated via journey.

He does *NOT* have any fields that contain delimited values. I don't know where you are seeing evidence of that in what he has shown.

He is using GROUP_CONCAT to get *OUTPUT* that is a delimited list, yes, but that's a reasonable thing to do.

millsy007
03-03-2009, 03:14 PM
Hi my tables were as Old Pedant said, but I am currently changing them!
My order by was in the wrong place, thanks

Old Pedant
03-03-2009, 08:26 PM
Not clear to me why you are changing the tables. They look okay to me.

It's possible that might not be the most efficient way, depending on how you *USE* the tables, but there's certainly nothing "wrong" with the design, as I diagnosed it. My opinion.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum