Как сортировать результат запроса по порядковому номеру дня недели, с понедельника до воскресенья?

Question

Если написать:

select hire_date, to_char(hire_date, 'fmDay') as "Day"
from hr.employees
order by "Day"
/

HIRE_DATE           Day                                         
------------------- --------------------------------------------
2005-10-30 00:00:00 Воскресенье                                 
[...]
2005-03-03 00:00:00 Четверг

То результат отсортирован по дням недели, с Воскресенья, затем Вторник и последний Четверг, то есть символьная сортировка.

Но мне нужно сортировать по порядковому номеру дня недели, т.е. с понедельника до воскресенья.

_{Свободный перевод вопроса Sort by day of the week from Monday to Sunday от участника @debaonline4u}

Answer 1

Порядковый номер дня недели со значениями 1-7 можно получить с маской формата D:

with c (dt) as (
    select trunc(sysdate)+level from dual connect by level <= 7)
select to_char ( dt, 'd' ) num, to_char ( dt, 'day' ) day
from c
order by num
/

NUM DAY                                         
--- --------------------------------------------
1   понедельник                                 
2   вторник                                     
[...]
7   воскресенье

Но, эта сортировка зависит от национальной настройки NLS_TERRITORY. В США неделя начинается с воскресенья, а в большинстве остальных стран понедельник считают первым днём недели. Посмотрите, если меняется територия, то и запрос выше вернёт другой порядок записей:

alter session set nls_territory='america'
/

NUM DAY                                         
--- --------------------------------------------
1   воскресенье                                 
2   понедельник                                 
[...]
7   суббота

Так как, в функцию TO_CHAR нельзя передать явно параметр NLS_TERRITORY, то любое решение, полагающееся на маску формата D для сортировки, будет заведомо ошибочно!

В качестве решения, можно получить последний понедельник и вычесть его из заданой даты. Результат функции TRUNC с маской формата IW - начало недели ISO, он независим от национальных настроек и всегда будет последним понедельником:

with c (dt) as (
    select trunc(sysdate)+level from dual connect by level <= 7)
select dt-trunc (dt, 'iw') num, to_char (dt, 'day') day
from c
order by dt-trunc(dt, 'iw')
/
NUM DAY                                         
--- --------------------------------------------
  0 понедельник                                 
  1 вторник                                     
[...]
  6 воскресенье

БЛОГ НА HUSL

Как сортировать результат запроса по порядковому номеру дня недели, с понедельника до воскресенья?

Ответы (1 шт):