Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unanswered: "order by" doesnt behave the way I need

    Hello guys, got a small proble,(I hope it's small)

    I need to get my results ordered alphabetically BUT theres a small detail

    when some strings in the field I am ordering by contains some white or blank spaces, PSQL does not group them

    Here is an example of what I have and what I want to have

    create table test(
    id int primary key,
    data text);


    The data in my table is:

    id | data
    1 | John
    2 | Julian
    3 | Johnny
    4 | John Edward
    5 | John Joe
    6 | Juliette
    7 | John Peter
    8 | Johnk
    9 | John Tracy
    10 | Johnd


    If I do: "select data from test order by data;"

    John
    John Edward
    Johnd
    John Joe
    Johnk
    Johnny
    John Peter
    John Tracy
    Julian
    Juliette


    What I Need and dont know how to do:

    John
    John Edward
    John Joe
    John Peter
    John Tracy
    Johnd
    Johnk
    Johnny
    Julian
    Juliette




    As you can see, what I want is to group the John's that have a white space following it before starting to list the John* that follow alphabetically (Johnd, Johnk, Johnny, etc)

  2. #2
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Try this
    Code:
    select * 
    from test
    order by replace(data, ' ', '0')

  3. #3
    Join Date
    Dec 2003
    Posts
    11
    it works!!! thanks!!

    But I dont understand how it works, I havent figured it out

    I got the example from the psql docs:
    replace('abcdefabcdef', 'cd', 'XX')

    has the result:
    abXXefabXXe


    so how come I dont have zero's all over where the blank spaces used to be?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by bitar
    so how come I dont have zero's all over where the blank spaces used to be?
    Because it's only used for the ORDER BY.
    In the SELECT list, you still have the original value.

  5. #5
    Join Date
    Dec 2003
    Posts
    11
    now I get it, thank you shammat

Posting Permissions

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