I want to rank on ID and value columns based on ascending order of UID. Expected output has to change once value column has a different value than the previous value. Ranks has to restart on each new ID
UID ID Value Expected Output
1 1 0 1
2 1 0 1
3 1 1 2
4 1 1 2
5 1 1 2
6 1 0 3
7 1 1 4
8 1 0 5
9 1 0 5
10 1 0 5
11 2 1 1
12 2 1 1
13 2 0 2
14 2 0 2
15 2 1 3
Here is a sample dataset that I have created:
CREATE TABLE [dbo].[Data] (
[UID] [int] NOT NULL,
[ID] [int] NULL,
[Value] [int] NULL
);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (1, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (2, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (3, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (4, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (5, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (6, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (7, 1, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (8, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (9, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (10, 1, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (11, 2, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (12, 2, 1);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (13, 2, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (14, 2, 0);
INSERT [dbo].[Data] ([UID], [ID], [Value]) VALUES (15, 2, 1);
=RANK(number,ref,[order]) The RANK function uses the following arguments: Number (required argument) – This is the value for which we need to find the rank. Ref (required argument) – Can be a list of, or an array of, or reference to, numbers.
To have cumulative totals, just add up the values as you go.
Conditional Formula to use it as RANKIF First of all, add a new column at the end of the table and name it “Subject Wise Rank”. in the D4 cell, enter this formula =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 and hit enter. After that, apply that formula to the end of the column, up to the last cell.
I think that the simplest approach to this gaps-and-islands problem is to use lag()
to retrieve the "previous" value, and then a window sum that increments everytime the value changes.
select uid, id, value,
1 + sum(case when value <> lag_value then 1 else 0 end)
over(partition by id order by uid) grp
from (
select d.*, lag(value, 1, value) over(partition by id order by uid) lag_value
from data d
) d
order by uid
Demo on DB Fiddle:
uid | id | value | grp --: | -: | ----: | --: 1 | 1 | 0 | 1 2 | 1 | 0 | 1 3 | 1 | 1 | 2 4 | 1 | 1 | 2 5 | 1 | 1 | 2 6 | 1 | 0 | 3 7 | 1 | 1 | 4 8 | 1 | 0 | 5 9 | 1 | 0 | 5 10 | 1 | 0 | 5 11 | 2 | 1 | 1 12 | 2 | 1 | 1 13 | 2 | 0 | 2 14 | 2 | 0 | 2 15 | 2 | 1 | 3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With