Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Feb 2009
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Ordering Contents of a Field Alphabetically

    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 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)

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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:
    Code:
    GROUP_CONCAT(passenger_name SEPARATOR '|')
    See the docs:
    http://dev.mysql.com/doc/refman/5.1/...n_group-concat

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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:
    Code:
    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/...n_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.

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by Old Pedant View Post
    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:


    Quote Originally Posted by millsy007 View Post
    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.

    Edit:
    @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
    Last edited by bazz; 03-02-2009 at 12:33 AM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    I'm sorry, Bazz, but I still think you are misdiagnosing what his tables look like.

    I see them looking like this:
    Code:
    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.

  • #7
    New Coder
    Join Date
    Feb 2009
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi my tables were as Old Pedant said, but I am currently changing them!
    My order by was in the wrong place, thanks

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •