Post reply

Order by

  • February 9, 2009 at 12:05 pm

    #940715

    Judy (2/9/2009)

    arun.sas (2/9/2009)

    Tray this

    simple

    Create table #t(rating int,bookid int)

    insert into #t

    select 97, 201

    union all

    select 96,100

    union all

    select 95,300

    union all

    select 50,100

    union all

    select 10,201

    union all

    select 30,300

    select a.rating,b.bookid from #t a, #t b

    where a.rating = b.rating

    order by

    b.bookid asc ,

    a.rating desc

    RESULT;

    RI BI

    96 100

    50 100

    97 201

    10 201

    95 300

    30 300

    The result you provided is not that I wanted. the result what I wanted is like below.

    RESULT;

    RI BI

    97 201

    10 201

    96 100

    50 100

    95 300

    30 300

    Perhaps if you explain the logic behind the sort you are looking for, it would help us figure out what needs to be done. It sort of eludes me at the moment.

  • February 9, 2009 at 12:28 pm

    #940732

    Thanks. I will try my best to explain clearly. The resultset will always be sorted by "Rating" decending. but if same bookid has two rating, one is very high, another is much low number . They need to be put together as a group. even though the rating is low, this record will still show before other records because it has another higher rating which is higher than other rating.

  • February 9, 2009 at 12:44 pm

    #940743

    Looking back through the posts, it appears tha Adi has already provided a working solution, so I won't look into this further at this time.

  • February 9, 2009 at 12:44 pm

    #940744

    So, what was wrong with my solution?

    _____________
    Code for TallyGenerator

  • February 9, 2009 at 1:09 pm

    #940768

    Sergiy (2/9/2009)

    So, what was wrong with my solution?

    Actually, Sergiy, I have no idea. Since Adi posted what appears to be a working solution, I have tried yours nor worked on one myself.

  • February 9, 2009 at 1:18 pm

    #940774

    Lynn Pettis (2/9/2009)

    Sergiy (2/9/2009)

    So, what was wrong with my solution?

    Actually, Sergiy, I have no idea.

    Actually, the question was not for you.

    🙂

    You may realize that if you look at the timing of our posts. 😉

    _____________
    Code for TallyGenerator

  • February 9, 2009 at 1:30 pm

    #940783

    Sergiy (2/9/2009)

    Lynn Pettis (2/9/2009)

    Sergiy (2/9/2009)

    So, what was wrong with my solution?

    Actually, Sergiy, I have no idea.

    Actually, the question was not for you.

    🙂

    You may realize that if you look at the timing of our posts. 😉

    Sergiy, you take things too seriously. I knew the question wasn't directed to me, I was just giving an answer. I'd be interested in the OP's response as well.

  • February 9, 2009 at 3:34 pm

    #940872

    2 smiles per post, considering it's MY post - is to seriously???

    :w00t:

    Man...

    :hehe:

    _____________
    Code for TallyGenerator

  • February 9, 2009 at 3:42 pm

    #940875

    Sergiy (2/9/2009)

    2 smiles per post, considering it's MY post - is to seriously???

    :w00t:

    Man...

    :hehe:

    Alright, seeing you laugh makes it all worthwhile! :w00t:

  • February 10, 2009 at 3:51 am

    #941065

    Sergiy (2/9/2009)

    So, what was wrong with my solution?

    I thought your solution was just a step-by-step explanation of Adi's.

    Derek

  • February 10, 2009 at 4:01 am

    #941071

    Derek Dongray (2/10/2009)

    Sergiy (2/9/2009)

    So, what was wrong with my solution?

    I thought your solution was just a step-by-step explanation of Adi's.

    Yep, kind of.

    Just missed his post.

    So, the question then: what was wrong with Adi's solution?

    _____________
    Code for TallyGenerator

  • February 10, 2009 at 4:35 am

    #941081

    Sergiy (2/10/2009)

    Derek Dongray (2/10/2009)

    Sergiy (2/9/2009)

    So, what was wrong with my solution?

    I thought your solution was just a step-by-step explanation of Adi's.

    Yep, kind of.

    Just missed his post.

    So, the question then: what was wrong with Adi's solution?

    Nothing as far as I could see, which is why I said it was the one needed.

    Derek Dongray (2/9/2009)

    No. From Judy's description, Adi's solution is needed.

    I just couldn't see how you worked out from the sample data, prior to Judy's explanation, that the result set was ordered by MaxRating desc with other ratings grouped by BookId after each MaxRating.

    My interpretation of the sample data was that the results were simply ordered by BookId asc and the Rating desc which would actually satisfy both samples, but doesn't happen to agree with Judy's explanation.

    Derek

  • February 10, 2009 at 10:57 pm

    #941844

    Judy, are you all set with this?

    --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)
    Intro to Tally Tables and Functions

  • February 11, 2009 at 2:03 pm

    #942483

    Thanks For all your answers. I have use Adi Cohn's solution on my stored procedure. It works perfect and just meet my requirement.

  • February 11, 2009 at 5:09 pm

    #942563

    Thanks for the feedback, Judy.

    --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)
    Intro to Tally Tables and Functions

Viewing 15 posts - 16 through 29 (of 29 total)

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