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 6 of 6

Thread: Sorting Resuts

  1. #1
    New Coder
    Join Date
    Mar 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sorting Resuts

    Hi all, i'm trying to figure out how to display the results of people so that the names are next to each other, which works great simply by using a simple select statement and ordering it by name, but to complicate matters I also want to retain the id order, 1,2,3 etc. example below

    id name
    1 Luke
    2 Rachel
    3 Craig
    4 Luke

    desired results im looking for,

    id name
    1 Luke
    4 Luke
    2 Rachel
    3 Craig
    5 Sarah
    6 John

    Would I need to sort the result first and then pass them to a temporary table for further sorting?


    Thanks for looking.
    Last edited by utstrangelove; 05-10-2011 at 05:28 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you can use an ORDER BY clause on more than one name

    Code:
    SELECT
      id,
      name
    FROM
      yourtablename
    ORDER BY
      name,
      id

  • #3
    New Coder
    Join Date
    Mar 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the response but that doesn't do what i'm looking for unfortunately.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    So you want to have them in order of the *LOWEST* number for each person, right?

    Code:
    SELECT P1.*, P2.minid 
    FROM people AS P1, 
       ( SELECT MIN(id) AS minid, name FROM people GROUP BY name ) AS P2
    WHERE P1.name = P2.name
    ORDER BY P2.minid
    But fair warning: If you have two unrelated people with the same name, this will lump them together.

    It's an okay solution for a small table, but if you had 100,000 people in your table, the likelihood is you would have some duplicated names that are not the same person.

    If you have a *DIFFERENT* field that is more likely to be unique than is name, it would be better to use it instead of name. Or in addition to name.

    For example, phone number. Joe Brown and Mary Brown might have the same phone number, so you can't use the phone number alone, but...
    Code:
    SELECT P1.*, P2.minid 
    FROM people AS P1, 
       ( SELECT MIN(id) AS minid, name, phone FROM people GROUP BY name, phone ) AS P2
    WHERE P1.name = P2.name AND P1.phone = P2.phone
    ORDER BY P2.minid
    Or something along those lines.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Mar 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks old pendant I appreciate you trying to help with my problem.

    Just to confirm (using higher id numbers) if a persons has 2 usersname that are not next to each other them put them next to each other and also try and retain as much a the order of the id as much as possible.

    so 1,2,3,4,5,7,6,8,9,10,13,11,12,14 as an example

    id username
    230 adam123
    231 jamesXYZ <-
    232 steve123
    233 jamesXYZ <-
    234 luke123abc
    235 luke123abc
    236 rachel789


    id user_id
    230 adam123
    231 jamesXYZ <-
    233 jamesXYZ <-
    232 steve123
    234 luke123abc
    235 luke123abc
    236 rachel789

    Thanks for you time and energy

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,641 Times in 4,603 Posts
    Yes, I didn't think to make sure that the grouped names were still in numerical order, but that's easy: Just add to the ORDER BY:
    Code:
    SELECT P1.*, P2.minid 
    FROM people AS P1, 
       ( SELECT MIN(id) AS minid, name FROM people GROUP BY name ) AS P2
    WHERE P1.name = P2.name
    ORDER BY P2.minid, P1.id
    *probably* they would come out in that order, anyway, if id is the primary key field of the table, but if you put it in the ORDER BY it will be forced to come out that way.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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