lunes, 24 de marzo de 2014

How do I order my SQL result by days of the week starting with Monday as the first?

Hi Deenma, this's my answers for you.

Testing Lab: Oracle Database 12c in Oracle Linux 6
Good Luck.!!








SQL> connect demo/demo@pdb11
Connected.
SQL> desc order_days
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 DAY                                                VARCHAR2(15)
 POSITION                                           NUMBER
 DAY_WEEK                                           DATE

SQL> /
Enter value for day: sunday
Enter value for position: 1
Enter value for date: 09-march-2014
old   1: insert into order_days values('&day',&position, '&date')
new   1: insert into order_days values('sunday',1, '09-march-2014')

1 row created.

SQL> /
Enter value for day: monday
Enter value for position: 2
Enter value for date: 10-march-2014
old   1: insert into order_days values('&day',&position, '&date')
new   1: insert into order_days values('monday',2, '10-march-2014')

1 row created.

SQL> /
Enter value for day: tuesday
Enter value for position: 3
Enter value for date: 11-march-2014
old   1: insert into order_days values('&day',&position, '&date')
new   1: insert into order_days values('tuesday',3, '11-march-2014')

1 row created.

SQL> /
Enter value for day: wednesday
Enter value for position: 4
Enter value for date: 12-march-2014
old   1: insert into order_days values('&day',&position, '&date')
new   1: insert into order_days values('wednesday',4, '12-march-2014')

1 row created.

SQL> /
Enter value for day: thursday
Enter value for position: 5
Enter value for date: 13-march-2014
old   1: insert into order_days values('&day',&position, '&date')
new   1: insert into order_days values('thursday',5, '13-march-2014')

1 row created.

SQL> /
Enter value for day: friday
Enter value for position: 6
Enter value for date: 14-march-2014
old   1: insert into order_days values('&day',&position, '&date')
new   1: insert into order_days values('friday',6, '14-march-2014')

1 row created.

SQL> /
Enter value for day: saturday
Enter value for position: 7
Enter value for date: 15-march-2014
old   1: insert into order_days values('&day',&position, '&date')
new   1: insert into order_days values('saturday',7, '15-march-2014')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from order_days;

DAY               POSITION DAY_WEEK
--------------- ---------- ---------
sunday                   1 09-MAR-14
monday                   2 10-MAR-14
tuesday                  3 11-MAR-14
wednesday                4 12-MAR-14
thursday                 5 13-MAR-14
friday                   6 14-MAR-14
saturday                 7 15-MAR-14

7 rows selected.

Normal query
SQL> select day, position from order_days
  2  order by to_char(day_week,'D');

DAY               POSITION
--------------- ----------
sunday                   1
monday                   2
tuesday                  3
wednesday                4
thursday                 5
friday                   6
saturday                 7

7 rows selected.

Solution for your question
SQL> select day, position from order_days
  2  order by to_char(day_week-1,'D');

DAY               POSITION
--------------- ----------
monday                   2
tuesday                  3
wednesday                4
thursday                 5
friday                   6
saturday                 7
sunday                   1

7 rows selected.

SQL>