How to order by in this case?

  • my data like this

    id | username | event

    1 a 1

    2 b 1

    3 a 2

    4 c 1

    5 a 3

    6 c 2

    7 b 2

    8 c 3

    9 b 3

    i want group by username and order by max(id)

    result like this

    username id

    ---------------

    b 9

    b 7

    b 2

    c 8

    c 6

    c 4

    a 5

    a 3

    a 1

    ---------------

    if insert new data : 10 a 4

    then result like this

    username id

    ---------------

    a 10

    a 5

    a 3

    a 1

    b 9

    b 7

    b 2

    c 8

    c 6

    c 4

    ---------------

    if insert new data : 11 b 4

    then result like this

    username id

    ---------------

    b 11

    b 9

    b 7

    b 2

    a 10

    a 5

    a 3

    a 1

    c 8

    c 6

    c 4

    ---------------

    sorry , my english is a little bit

    and thank you very for answer me

  • here's one solution:

    select A.username, A.id

    from your_table as A

    join (select username, max(id) as max_id

    from your_table group by username) as B

    on A.username = B.username

    order by B.max_id desc, A.username, A.id desc

  • I have to know because it's a bit unusual... why do you need this type of sort?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • antonio.collins (3/7/2008)


    here's one solution:

    select A.username, A.id

    from your_table as A

    join (select username, max(id) as max_id

    from your_table group by username) as B

    on A.username = B.username

    order by B.max_id desc, A.username, A.id desc

    oh!! thank you very much :w00t:

  • Yep... Antonio writes good stuff. Still, I'd like to know why you need this type of "sort", please.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • looks like he is trying to get the user with the most recent activity to the top of the resultset.

  • Heh... yeah... I can see that... my question would be why? And if what you say were true, why list all of the users instead of just the most recent?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Very useful Query. Thanks a lot:)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply