Given this data how can give rank for each repeating data. 1 to 5 i want to rank as 1 and next 1 to 5 i want to rank as 2

Data

1
2
3
4
5
1
2
3
4
5

Expecting output

Data | Column

1     1
2     1
3     1 
4     1  
5     1
1     2
2     2
3     2
4     2
5     2

CodePudding user response：

You're probably better off asking the question you actually have.

The result you want can be achieved from the data you gave, but it's going to be non-deterministic.

DECLARE @ints TABLE (INT INT)
INSERT INTO @ints (INT) VALUES
(1), (2), (3), (4), (5),
(1), (2), (3), (4), (5)

SELECT INT, ROW_NUMBER() OVER (PARTITION BY INT ORDER BY INT) AS rn
  FROM @ints
 ORDER BY rn, INT

INT rn
------
1   1
2   1
3   1
4   1
5   1
1   2
2   2
3   2
4   2
5   2

CodePudding user response：

Best you can do with the limited sample data is to create a row number for each time a number appears. Then order by the row number and then the number. If this doesn't work, then show us more real data.

When using ROW_NUMBER, there's no guarantee that the first 1 through 5 group will be ordered correctly. You have to have some other identifier to guarantee the ordering (i.e. time stamp, set number, parent group, etc.).

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE Numbers (
  num int not null
  );
  
INSERT INTO Numbers
VALUES (1),(2),(3),(4),(5),(1),(2),(3),(4),(5)

Query 1:

WITH prelim AS (
  SELECT n.num
    , ROW_NUMBER() OVER(PARTITION BY n.num ORDER BY n.num ASC) as row_num
  FROM Numbers as n
)
SELECT
  p.num
  , p.row_num
FROM prelim as p
ORDER BY p.row_num, p.num

Results:

| num | row_num |
|-----|---------|
|   1 |       1 |
|   2 |       1 |
|   3 |       1 |
|   4 |       1 |
|   5 |       1 |
|   1 |       2 |
|   2 |       2 |
|   3 |       2 |
|   4 |       2 |
|   5 |       2 |