add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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