'Numerate values in a column regardless of order

I have a table like this:

Date Week
2021-01-01 53
2021-01-02 53
2021-01-03 53
2021-01-04 1
2021-01-05 1
2021-01-06 1
2021-01-07 1
... ...
2021-12-30 52
2021-12-31 52

I want to rank weeks not with their values but with Date ascending order. I tried to use

dense_rank() over (order by Week)

and got this results:

Date Week
2021-01-01 53
2021-01-02 53
2021-01-03 53
2021-01-04 1
2021-01-05 1
2021-01-06 1
2021-01-07 1
... ...
2021-12-30 52
2021-12-31 52

But 53rd week is on 53rd rank, not 1st as I want. Do you know what I need to use in that case? Thx

sqlvertica


Solution 1:[1]

You can try to use MOD function in ORDER BY.

Because the Week Number seem like between 1 to 53, MOD function will calculate

  • MOD(53, 53)=> 0
  • MOD(1, 53) => 1

so on .... .

dense_rank() over (order by MOD(Week, 53))

Solution 2:[2]

use order by desc

 select *, row_number()over(order by week desc) from table_name

Solution 3:[3]

You can simply play with Vertica's date/time functions - and add @D-Shih 's clever idea with the modulo function to it, and no dense-rank needed if the result is the one you display:

WITH
indata (dt) AS (
            SELECT DATE '2020-12-30'
  UNION ALL SELECT DATE '2020-12-31'
  UNION ALL SELECT DATE '2021-01-01'
  UNION ALL SELECT DATE '2021-01-02'
  UNION ALL SELECT DATE '2021-01-03'
  UNION ALL SELECT DATE '2021-01-04'
  UNION ALL SELECT DATE '2021-01-05'
[...]
  UNION ALL SELECT DATE '2021-12-30'
  UNION ALL SELECT DATE '2021-12-31'
  UNION ALL SELECT DATE '2022-01-01'
  UNION ALL SELECT DATE '2022-01-02'
  UNION ALL SELECT DATE '2022-01-03'
  UNION ALL SELECT DATE '2022-01-04'
)
SELECT
  dt  
, WEEK(dt)             AS stdweek
, WEEK_ISO(dt)         AS isoweek
, MOD(WEEK(dt),53)     AS stdwkmod53
, MOD(WEEK_ISO(dt),53) AS isowkmod53
FROM indata;
-- out      dt     | stdweek | isoweek | stdwkmod53 | isowkmod53 
-- out ------------+---------+---------+------------+------------
-- out  2020-12-30 |      53 |      53 |          0 |          0
-- out  2020-12-31 |      53 |      53 |          0 |          0
-- out  2021-01-01 |       1 |      53 |          1 |          0
-- out  2021-01-02 |       1 |      53 |          1 |          0
-- out  2021-01-03 |       2 |      53 |          2 |          0
-- out  2021-01-04 |       2 |       1 |          2 |          1
-- out  2021-01-05 |       2 |       1 |          2 |          1
[...]
-- out  2021-12-30 |      53 |      52 |          0 |         52
-- out  2021-12-31 |      53 |      52 |          0 |         52
-- out  2022-01-01 |       1 |      52 |          1 |         52
-- out  2022-01-02 |       2 |      52 |          2 |         52
-- out  2022-01-03 |       2 |       1 |          2 |          1
-- out  2022-01-04 |       2 |       1 |          2 |          1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih
Solution 2 Luca Kiebel
Solution 3 marcothesane

Related Questions