4

I want to generate continuously number with the combination of 2 columns and in batch size of 5. Anybody can help to solve this?

This is my input and desired output is given:

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Paras Golakiya
  • 51
  • 4
  • You have a Typo. Rank is `30` on the left and `20` on the right, for the third from last row of `Paras`. – MatBailie Nov 20 '18 at 12:13
  • Oh yeah. Thanks for correction. I am going to update it. – Paras Golakiya Nov 20 '18 at 12:23
  • Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) – Zohar Peled Nov 20 '18 at 12:27

2 Answers2

5

An adoption of @GordonLinoff's answer...

SELECT
    name,
    rank, 
    DENSE_RANK() OVER (ORDER BY name DESC, Rank, ((seqnum - 1) / 5))  AS rno   
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY name, rank ORDER BY (SELECT null))   AS seqnum
    FROM
        yourTable
)
    sequenced
ORDER BY
    3
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • 1
    Thanks for your answer and your answer is totally correct. Thanks again it will helps me a lot. Can you please correct your answer due to some little syntax error like " ROW_NUMBER() OVER " instead of " ROW_NUMBER() AS " – Paras Golakiya Nov 20 '18 at 12:29
  • @gordonLinoff - Thanks – MatBailie Nov 20 '18 at 13:13
1

You can use row_number() and arithmetic:

select name, rank, 
       ((seqnum - 1) / 5) + 1 as rno
from (select t.*,
              row_number() as (partition by name, rank order by (select null)) as seqnum
      from t
     ) t
order by seqnum;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Looking at the OPs example, there should be no partitioning, just ordering. *(The `Rno` never resets to 1)*. I think this would need at least two levels of analytic functions, such as applying `DENSE_RANK() OVER (ORDER BY name DESC, Rank, ((seqnum - 1) / 5))` in the outer query? – MatBailie Nov 20 '18 at 12:14
  • Thanks for your response. But my desired output is different from which you answered. Please see our Input and their corresponding output in deeply. If any name has been changed then also Rno should be increase from that max Rno. It have not to start from 1. – Paras Golakiya Nov 20 '18 at 12:17