How to return top n and sum up the rest

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 12, 2015 at 8:49 am

  #1777271

  sonchoix (2/12/2015)

  ---

  dwain.c (2/11/2015)

  ---

  In the TOP 2 or 3 or 4 you are listing (outside of the Others group), what do you want to do if there are ties?

  This looks to me like a TOP 5 customers report or something, and while it is unlikely that you'd get a tie on revenue by customer, it is not entirely inconceivable and you wouldn't want to give a customer short shrift and knock them out of the rankings just because there was coincidentally a tie.

  I appreciate your reply, at this moment, there is no need to over complicate the case, but you indicate a very important point and I will definitely keep that in mind later.

  Thanks.

  There's nothing complicated on adjusting the code for this, you just need to use a different Ranking function. 😉

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• MMartin1

  One Orange Chip

  Points: 27799

  More actions

  February 23, 2015 at 5:35 pm

  #1779453

  Luis Cazares (2/12/2015)

  ---

  sonchoix (2/12/2015)

  ---

  dwain.c (2/11/2015)

  ---

  In the TOP 2 or 3 or 4 you are listing (outside of the Others group), what do you want to do if there are ties?

  This looks to me like a TOP 5 customers report or something, and while it is unlikely that you'd get a tie on revenue by customer, it is not entirely inconceivable and you wouldn't want to give a customer short shrift and knock them out of the rankings just because there was coincidentally a tie.

  I appreciate your reply, at this moment, there is no need to over complicate the case, but you indicate a very important point and I will definitely keep that in mind later.

  Thanks.

  There's nothing complicated on adjusting the code for this, you just need to use a different Ranking function. 😉

  I suspect this is a homework problem.

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

• MMartin1

  One Orange Chip

  Points: 27799

  More actions

  February 23, 2015 at 5:39 pm

  #1779455

  CELKO (2/12/2015)

  ---

  it is not entirely inconceivable and you wouldn't want to give a customer short shrift and knock them out of the rankings just because there was coincidentally a tie.

  I was looking at some of my old stuff, and realized that ROW_NUMBER() should have been DENSE_RANK() more often than not :crying:

  I will certainly keep this in mind the next time I think I need to use ROW_NUMBER()

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

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  February 23, 2015 at 8:09 pm

  #1779470

  Heh... deleted my post. I was having too much fun. 🙂

  --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)