Rank/denserank in SQL

user_16542396 0
Viewed 39

Data:

create table chr
(
  id int,
  dttime datetime,
  inventory varchar(10),
  expensive varchar(2)
)

insert into chr values
(001,      '2022-06-21 08:54:07', 'item1', '0'),
(001,      '2022-06-21 08:54:07', 'item1', '0'),
(001,      '2022-06-21 08:54:07', 'item1', '0'),
(001,      '2022-06-21 08:54:07', 'item1', '0'),
(001,      '2022-06-21 08:54:07', 'item1', '0'),
(001,      '2022-06-21 08:54:07', 'item1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '1'),
(001,      '2022-06-21 08:54:19', 'item1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '1'),
(001,      '2022-06-21 08:54:19', 'item1', '0'),
(001,      '2022-06-21 08:55:16', 'item1', '0'),
(001,      '2022-06-21 08:55:16', 'item1', '0')

Code:

select *,
(case when expensive = 0 then dense_rank() over(partition by id,inventory,expensive order by dttime asc) 
when expensive = 1 then 0 end)
from chr

Current result:

create table chr
(
  id int,
  dttime datetime,
  inventory varchar(10),
  expensive varchar(2),
  rank varchar(2)
)

insert into chr values
(001,      '2022-06-21 08:54:07', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:19', 'item1', '1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '2'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '2'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '2'),
(001,      '2022-06-21 08:54:19', 'item1', '1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '2'),
(001,      '2022-06-21 08:55:16', 'item1', '0', '3'),
(001,      '2022-06-21 08:55:16', 'item1', '0', '3')

Desired result:

create table chr
(
  id int,
  dttime datetime,
  inventory varchar(10),
  expensive varchar(2),
  rank varchar(2)
)

insert into chr values
(001,      '2022-06-21 08:54:07', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '2'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '3'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '4'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '5'),
(001,      '2022-06-21 08:54:07', 'item1', '0', '6'),
(001,      '2022-06-21 08:54:19', 'item1', '1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '1'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '2'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '3'),
(001,      '2022-06-21 08:54:19', 'item1', '1', '0'),
(001,      '2022-06-21 08:54:19', 'item1', '0', '1'),
(001,      '2022-06-21 08:55:16', 'item1', '0', '2'),
(001,      '2022-06-21 08:55:16', 'item1', '0', '3')

I try to use dense_rank to achieve the result that I want by trying to partition based on 'expensive' column. so the partition will 'break' when expensive = 1 and the next row then it will reset the number until the next expensive = 1. but somehow when I run the code with order by clause, it will partition according to dttime. looking forward to any solution to achieve my desired result or if anyone can solve the issue with dense_rank.

window-functions postgresql sql
1 Answers

You can use ROW_NUMBER(). For example:

select *,
  case when expensive = 0
       then row_number() over(partition by id, inventory, dttime
                                  order by expensive
            )
       else 0
  end as rn
from chr

Note: your table lacks a primary key, something that would facilitate the processing of rows.

user_64361910
Related