Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Red face Unanswered: ORDER BY / GROUP BY troubles

    I have a table 'TEST' with two columns:

    theCharacter CHAR(1)
    theNumber NUMBER(1)

    i have the following records in the table:

    'B', 1
    'B', 2
    'B', 3
    'B', 4
    'A', 5
    'A', 6
    'A', 7
    'B', 8
    'B', 9

    How do I build my query to get the following resultorder:

    'B', 1
    'B', 2
    'B', 3
    'B', 4
    'B', 8
    'B', 9
    'A', 5
    'A', 6
    'A', 7

    so that the result is grouped by theCharacter and ordered by theNumber?

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: ORDER BY / GROUP BY troubles

    Originally posted by Schnaaf
    I have a table 'TEST' with two columns:

    theCharacter CHAR(1)
    theNumber NUMBER(1)

    i have the following records in the table:

    'B', 1
    'B', 2
    'B', 3
    'B', 4
    'A', 5
    'A', 6
    'A', 7
    'B', 8
    'B', 9

    How do I build my query to get the following resultorder:

    'B', 1
    'B', 2
    'B', 3
    'B', 4
    'B', 8
    'B', 9
    'A', 5
    'A', 6
    'A', 7

    so that the result is grouped by theCharacter and ordered by theNumber?
    select character, number from test order by character desc, number asc

  3. #3
    Join Date
    Apr 2004
    Posts
    6

    Re: ORDER BY / GROUP BY troubles

    Originally posted by ika
    select character, number from test order by character desc, number asc
    Thats not quite it. because if I would have the following recordsets:

    "A", 1
    "A", 2
    "A", 3
    "B", 4
    "B", 5
    "B", 6
    "A", 7
    "A", 8
    "A", 9

    I would like to get the result in the follwing order:

    "A", 1
    "A", 2
    "A", 3
    "A", 7
    "A", 8
    "A", 9
    "B", 4
    "B", 5
    "B", 6

    IKA, your sollution would return the following:

    "B", 4
    "B", 5
    "B", 6
    "A", 1
    "A", 2
    "A", 3
    "A", 7
    "A", 8
    "A", 9

  4. #4
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: ORDER BY / GROUP BY troubles

    Originally posted by Schnaaf
    Thats not quite it. because if I would have the following recordsets:



    I would like to get the result in the follwing order:

    "A", 1
    "A", 2
    "A", 3
    "A", 7
    "A", 8
    "A", 9
    "B", 4
    "B", 5
    "B", 6

    IKA, your sollution would return the following:

    "B", 4
    "B", 5
    "B", 6
    "A", 1
    "A", 2
    "A", 3
    "A", 7
    "A", 8
    "A", 9

    If you have following records in the table TEST:

    "A", 1
    "A", 2
    "A", 3
    "B", 4
    "B", 5
    "B", 6
    "A", 7
    "A", 8
    "A", 9

    then "select * from test order by charact asc, nr asc" must return following:

    "A", 1
    "A", 2
    "A", 3
    "A", 7
    "A", 8
    "A", 9
    "B", 4
    "B", 5
    "B", 6

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Schnaaf, that's not fair, in your first post you specifically said you wanted the Bs ahead of the As, and that requires desc for theCharacter in the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Posts
    6
    Originally posted by r937
    Schnaaf, that's not fair, in your first post you specifically said you wanted the Bs ahead of the As, and that requires desc for theCharacter in the ORDER BY
    In my first post I specifically asked for a sollution so that the result is grouped by theCharacter and ordered by theNumber.

    I need a query sollution that will fit for all data. The given data was just an example, nothing more.

    As I said before:

    "so that the result is grouped by theCharacter and ordered by theNumber?"

    Maybe I didnt express myself right.

    What I want is all characters grouped, thus all A's together and all B's together and all C's together, ect (but not nescissarily in that order). The order should be determined by the first number that belongs to every letter.

    Look at theNumber column as if it is an auto_increment column. But with every insert the theCharachter column can cantain a different character.


    So, to make my dataset adequate, this is my testdata:

    "A", 1
    "A", 2
    "C", 3
    "B", 4
    "B", 5
    "B", 6
    "A", 7
    "C", 8
    "B", 9

    And I need the following result:
    "A", 1
    "A", 2
    "A", 7
    "C", 3
    "C", 8
    "B", 4
    "B", 5
    "B", 6
    "B", 9

    Note that my needs haven't changed in all three examples, but maybe it wasnt exclusive enough. Hope somebody has an idea.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "The order should be determined by the first number that belongs to every letter"

    wow, do you realize how much more difficult this will make the solution

    are you sure this is what you want?

    because the query will be extremely complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Posts
    6
    I know,

    That's why I ask the question here.

    I have been a database programmer for 5 years on Oracle and on MySQL for 1 year now and I don't have a clue on how to handle this.

    The sollution smells like subselects, but even with subselects I wouldnt know how to solve it.

  9. #9
    Join Date
    Apr 2004
    Posts
    6
    For convenience I will exlplain why I need this kind of sorting/grouping.

    I am working on a stats module for our CMS.

    look at the theCharacter as a (PHP)sessionID value
    look at the theNumber as a timestamp value

    I want an overview of the timestamps (a request) grouped together per sessionID while maintaining the original order of the sessioncreation.

    "SESSION_ID_1", "20040402120000"
    "SESSION_ID_1", "20040402120100"
    "SESSION_ID_2", "20040402120200"
    "SESSION_ID_3", "20040402120300"
    "SESSION_ID_2", "20040402120400"
    "SESSION_ID_2", "20040402120500"
    "SESSION_ID_1", "20040402120600"
    "SESSION_ID_3", "20040402120700"
    "SESSION_ID_2", "20040402120800"
    "SESSION_ID_3", "20040402120900"

    should result in:

    "SESSION_ID_1", "20040402120000"
    "SESSION_ID_1", "20040402120100"
    "SESSION_ID_1", "20040402120600"
    "SESSION_ID_2", "20040402120200"
    "SESSION_ID_2", "20040402120400"
    "SESSION_ID_2", "20040402120500"
    "SESSION_ID_2", "20040402120800"
    "SESSION_ID_3", "20040402120700"
    "SESSION_ID_3", "20040402120300"
    "SESSION_ID_3", "20040402120900"

    this way I can retrieve the order in which the sessions have been created in time plus I can learn about the clickingorder in time per session ID

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    even with subselects you have a huge problem

    here's a suggested approach:

    create a temp table with an auto_number, sessionID, and timestamp

    insert into temptable
    select sessionID, MIN(timestamp) as firstrow
    from yourtable
    group by sessionID
    order by firstrow

    now join your main table back to this temp table, and use the auto_increment value as the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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