SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table yourtable (
 ID int identity(1,1) primary key, 
 Group1 int, 
 Value int
);
insert into yourtable (Group1, Value) values
  (10,100),(10,150),(10,150),(10,150),(10,360)
, (200,360),(200,420),(200,420),(200,500),(200,500)
10 rows affected

--
-- ROW_NUMBER (to get a sequence per group) 
--
select *
, ROW_NUMBER() OVER (PARTITION BY Group1 ORDER BY Value, ID) as Rownum
from yourtable
order by Group1, Rownum


ID
Group1
Value
Rownum


1
10
100
1

2
10
150
2

3
10
150
3

4
10
150
4

5
10
360
5

6
200
360
1

7
200
420
2

8
200
420
3

9
200
500
4

10
200
500
5


--
-- Emulating ROW_NUMBER via a correlated sub-query
--
select *, 
(select count(*) from yourtable t2 
 where t2.Group1 = t.Group1
   and (t2.Value < t.Value 
        or (t2.Value = t.Value 
            and t2.ID <= t.ID))
) as Rownum
from yourtable t
order by Group1, Rownum


ID
Group1
Value
Rownum


1
10
100
1

2
10
150
2

3
10
150
3

4
10
150
4

5
10
360
5

6
200
360
1

7
200
420
2

8
200
420
3

9
200
500
4

10
200
500
5


--
-- RANK (same values get same rank, but with gaps)
--
select *
, RANK() over (partition by Group1 order by Value) as Ranknum
from yourtable
order by Group1, Ranknum


ID
Group1
Value
Ranknum


1
10
100
1

2
10
150
2

3
10
150
2

4
10
150
2

5
10
360
5

6
200
360
1

7
200
420
2

8
200
420
2

9
200
500
4

10
200
500
4


--
-- Emulating RANK via a correlated sub-query
--
select *, 
(select count(t2.value)+1 from yourtable t2 
 where t2.Group1 = t.Group1
   and t2.Value < t.Value) as Ranknum
from yourtable t
order by Group1, Ranknum


ID
Group1
Value
Ranknum


1
10
100
1

2
10
150
2

3
10
150
2

4
10
150
2

5
10
360
5

6
200
360
1

7
200
420
2

8
200
420
2

9
200
500
4

10
200
500
4


--
-- DENSE_RANK (same values get same rank, without gaps)
--
select *
, DENSE_RANK() over (partition by Group1 order by Value) as Ranknum
from yourtable
order by Group1, Ranknum


ID
Group1
Value
Ranknum


1
10
100
1

2
10
150
2

3
10
150
2

4
10
150
2

5
10
360
3

6
200
360
1

7
200
420
2

8
200
420
2

9
200
500
3

10
200
500
3


--
-- Emulating DENSE_RANK via a correlated sub-query
--
select *, 
(select count(distinct t2.Value) from yourtable t2 
 where t2.Group1 = t.Group1
   and t2.Value <= t.Value
) as Ranknum
from yourtable t
order by Group1, Ranknum


ID
Group1
Value
Ranknum


1
10
100
1

2
10
150
2

3
10
150
2

4
10
150
2

5
10
360
3

6
200
360
1

7
200
420
2

8
200
420
2

9
200
500
3

10
200
500
3

ID	Group1	Value	Rownum
1	10	100	1
2	10	150	2
3	10	150	3
4	10	150	4
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	3
9	200	500	4
10	200	500	5

ID	Group1	Value	Rownum
1	10	100	1
2	10	150	2
3	10	150	3
4	10	150	4
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	3
9	200	500	4
10	200	500	5

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	4
10	200	500	4

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	4
10	200	500	4

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	3
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	3
10	200	500	3

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	3
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	3
10	200	500	3

ID	Group1	Value	Rownum
1	10	100	1
2	10	150	2
3	10	150	3
4	10	150	4
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	3
9	200	500	4
10	200	500	5

ID	Group1	Value	Rownum
1	10	100	1
2	10	150	2
3	10	150	3
4	10	150	4
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	3
9	200	500	4
10	200	500	5

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	4
10	200	500	4

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	4
10	200	500	4

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	3
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	3
10	200	500	3

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	3
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	3
10	200	500	3

ID	Group1	Value	Rownum
1	10	100	1
2	10	150	2
3	10	150	3
4	10	150	4
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	3
9	200	500	4
10	200	500	5

ID	Group1	Value	Rownum
1	10	100	1
2	10	150	2
3	10	150	3
4	10	150	4
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	3
9	200	500	4
10	200	500	5

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	4
10	200	500	4

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	5
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	4
10	200	500	4

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	3
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	3
10	200	500	3

ID	Group1	Value	Ranknum
1	10	100	1
2	10	150	2
3	10	150	2
4	10	150	2
5	10	360	3
6	200	360	1
7	200	420	2
8	200	420	2
9	200	500	3
10	200	500	3