how to rank row for same repeating value in sql

Question

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

Expecting output

Data | Column

I was trying to implement using row number but Below is the exact requirement that i have to implement :

Refcol value column

    1  refers to time
    2  refers to name
    3  refers to location
    4  refers to Available (1 or 0 or null)


ID | Refcol | Metric 
1     1       02/02/2022
1     2       Adam
1     3       Japan
1     4       1 
1     1       03/02/2022
1     2       Smith 
1     3       England 
1     4       0

Now i want to transform above data as shown below

Expected Ouput

ID | time        |  name | location | Available
1    02/02/2022     Adam    Japan      1
1    03/02/2022     Smith   England    0

Tim Jarosz · Accepted Answer

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 |

UPDATE: If you are dead-set on not changing the data structure, then the best you can do is loop through the data and assigning a unique SetID number to each group of 4 rows. There is no guarantee that this will work when you have more than 4 rows in the table since you have no column to guarantee a consistent sort order.

CREATE TABLE attributes (
  ID int not null
  , RefCol int not null
  , Metric nvarchar(50) not null
  , SetID int null
);

INSERT INTO attributes (ID, RefCol, Metric) 
VALUES 
  (1,1,'02/02/2022')
  ,(1,2,'Adam')
  ,(1,3,'Japan')
  ,(1,4,'1')
  ,(1,1,'03/02/2022')
  ,(1,2,'Smith')
  ,(1,3,'England')
  ,(1,4,'0')
;

DECLARE @setID int = 0;

WHILE (EXISTS (SELECT ID FROM attributes WHERE SetID is NULL))
BEGIN
  UPDATE TOP (4) attributes
  SET SetID = @setID
  FROM attributes
  WHERE SetID IS NULL
  ;

  SET @setID = @setID + 1;
END

SELECT * FROM attributes;

SELECT DISTINCT 
  a.SetID
  , a.ID
  , aTime.Metric as [time]
  , aName.Metric as [name]
  , aLoc.Metric as [location]
  , aAvail.Metric as [Available]
FROM attributes as a
  LEFT OUTER JOIN attributes as aTime
    ON aTime.SetID = a.SetID
    AND aTime.RefCol = 1
  LEFT OUTER JOIN attributes as aName
    ON aName.SetID = a.SetID
    AND aName.RefCol = 2
  LEFT OUTER JOIN attributes as aLoc
    ON aLoc.SetID = a.SetID
    AND aLoc.RefCol = 3
  LEFT OUTER JOIN attributes as aAvail
    ON aAvail.SetID = a.SetID
    AND aAvail.RefCol = 4
;

ID	RefCol	Metric	SetID
1	1	02/02/2022	0
1	2	Adam	0
1	3	Japan	0
1	4	1	0
1	1	03/02/2022	1
1	2	Smith	1
1	3	England	1
1	4	0	1

SetID	ID	time	name	location	Available
0	1	02/02/2022	Adam	Japan	1
1	1	03/02/2022	Smith	England	0

fiddle

how to rank row for same repeating value in sql

Answers (2)

Related Questions