Skip to main content

Sorting by day of the week

Have you thought of sorting by the day of the week.

Firstly you have to get the day of the week, check the statement below to get the day of the week.























If you try to order day of the week by using ORDER BY on column 3 which is a character column, ORDER BY treats this as text and sorts it from A-Z. So if your intention is to sort from MONDAY to SUNDAY, beginning with  MONDAY as day1 and SUNDAY as day7 then you need to use either CASE or DECODE functions.

Check an example with decode below.

























SQL STATEMENTS USED IN THE ARTICLE.


1. Statement from Picture1.

SELECT ename,hiredate,to_char(hiredate,'Day')as day FROM EMP;

2. Statement from Picture2.

SELECT ename,hiredate,to_char(hiredate,'Day')as day,
CASE
          WHEN to_char(hiredate,'fmDay') = 'Monday' THEN 1
          WHEN to_char(hiredate,'fmDay') = 'Tuesday' THEN 2
          WHEN to_char(hiredate,'fmDay') = 'Wednesday' THEN 3
          WHEN to_char(hiredate,'fmDay') = 'Thursday' THEN 4
          WHEN to_char(hiredate,'fmDay') = 'Friday' THEN 5
          WHEN to_char(hiredate,'fmDay') = 'Saturday' THEN 6
          WHEN to_char(hiredate,'fmDay') = 'Sunday' THEN 7
     END Ordering
FROM emp
ORDER BY 4;

3. Solution using DECODE Function.


SELECT ename,hiredate,to_char(hiredate,'Day')as day,
       DECODE(to_char(hiredate,'fmDay'),
                          'Monday',1,
                          'Tuesday',2,
                          'Wednesday',3,
                          'Thursday',4,
                          'Friday',5,
                          'Saturday',6,
                          'Sunday',7) Ordering
FROM emp
ORDER BY 4

4. Both CASE and DECODE functions can be used directly in ORDER BY, check the statements below.

CASE:

SELECT ename,hiredate,to_char(hiredate,'Day')as day
FROM emp
ORDER BY CASE
          WHEN to_char(hiredate,'fmDay') = 'Monday' THEN 1
          WHEN to_char(hiredate,'fmDay') = 'Tuesday' THEN 2
          WHEN to_char(hiredate,'fmDay') = 'Wednesday' THEN 3
          WHEN to_char(hiredate,'fmDay') = 'Thursday' THEN 4
          WHEN to_char(hiredate,'fmDay') = 'Friday' THEN 5
          WHEN to_char(hiredate,'fmDay') = 'Saturday' THEN 6
          WHEN to_char(hiredate,'fmDay') = 'Sunday' THEN 7
     END;

DECODE:

SELECT ename,hiredate,to_char(hiredate,'Day')as day
FROM emp
ORDER BY DECODE(to_char(hiredate,'fmDay'),
                          'Monday',1,
                          'Tuesday',2,
                          'Wednesday',3,
                          'Thursday',4,
                          'Friday',5,
                          'Saturday',6,
                          'Sunday',7);

Labels:

Comments

sandeep said…
Why order by 4 is used in case method is it ordering by column no. 4,
what if we use order by 3 in case method.
well thanks for the example and please do reply.
October 29, 2011 at 8:23 PM
sandeep said…
and can we order all columns at a time ie name, hiredate ,day,ordering or we need to write case for each column to be sorted
October 29, 2011 at 8:29 PM
divya said…
sir,i am not able to craete view on emp table..its giving error as insufficient privileges..wat s the error sir?
November 7, 2011 at 10:18 PM
Sonyandsavio said…
Sir, while installing oracle 10g ,after the abnormal error ,unknown,unknown,unknown comes and i click ok the window is just vanishing and nothing is happening afetr that.
Please guide me what i have to do.
December 2, 2011 at 9:45 AM
Sonyandsavio said…
I am runnning it as administrator.
December 2, 2011 at 9:48 AM
sree said…
sir,
i attended an interview last week, in that they asked this question
"Write an sql query to find the first week of a month?"
could u pls tell me the ans.....
January 5, 2012 at 11:41 AM
varadakorti said…
Hi Sir,
This is Varada(Back Up Faculty) sir i have following doubts
i)How to create the DB
ii)How to create the user
iii)How to give the access to the newly created user
iV)Display the employees who r earning salary more than there respective managers using subquries or joins
March 19, 2012 at 8:34 PM
abclst.blogspot.in said…
Creating User, Providing Roles,Assigning Privileges..
Creating a User:
CREATE USER username IDENTIFIED BY apassword;

EX: CREATE USER DINGA IDENTIFIED BY DINGI;

Providing Roles:
GRANT CONNECT TO username;

EX:GRANT CONNECT TO DINGA;

In some cases to create a more powerful user, you may also consider adding the RESOURCE role (allowing the user to create named types for custom schemas) or even the DBA role, which allows the user to not only create custom named types but alter and destroy them as well

GRANT CONNECT, RESOURCE, DBA TO USERNAME;

EX:GRANT CONNECT, RESOURCE, DBA TO DINGA;

Assigning Privileges:
We also need to ensure our new user has disk space allocated in the system to actually create or modify tables and data, so we’ll GRANT TABLESPACE like so:

GRANT UNLIMITED TABLESPACE TO USERNAME;

EX:GRANT UNLIMITED TABLESPACE TO DINGA;

Table Privileges:
GRANT SELECT [, INSERT] [, UPDATE] [, DELETE] on schema.table TO username;

EX: GRANT SELECT, INSERT,UPDATE,DELETE ON SCOTT.EMP TO DINGA;

Grant Select on all Tables Owned By Specific User
Select 'GRANT SELECT ON OwningUser.'||Table_Name||'TO ReceivingUser'
From All_Tables Where Owner='OWNINGUSER'

EX:
Select 'GRANT SELECT ON SCOTT.'||Table_Name||'TO DINGA'
From All_Tables Where Owner='SCOTT';
May 31, 2017 at 4:07 PM
abclst.blogspot.in said…
iv) SELECT A.ENAME EMPLOYEE,B.ENAME MANAGER
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND A.SAL>B.SAL
May 31, 2017 at 4:09 PM
abclst.blogspot.in said…
Login as system and execute below query
Grate create view to scott;
Then login as scott and create view
May 31, 2017 at 4:11 PM
Post a Comment

Popular posts from this blog

Basics of RDBMS

Data Small set of information becomes data, this set of information helps make decision. Data is always some useful information. Database Place where you store the data. Database represents some aspect of the real world called "miniworld". A database is designed, built and populated with data for a specific purpose. It has intended group of users and some preconceived applications in which these users are interested. In other words, a database has some source from which data is derived, some degree of interaction with events in the real world and an audience that is actively interested in the contents of the database. Database can also be defined as collection of one or more tables. Ex: Mobile, human brain etc DBMS (Database Management System ) Is a program that stores retrieves and modifies data in the database on request. Study of different techniques of design, development and maintenance of the database Types of DBMS These types are based upon their m
102 comments
Read more

SQL Interview Questions on Subqueries

SUB Queries: 1. List the employees working in research department 2. List employees who are located in New York and Chicago 3. Display the department name in which ANALYSTS are working 4. Display employees who are reporting to JONES 5. Display all the employees who are reporting to Jones Manager 6. Display all the managers in SALES and ACCOUNTING department 7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them 8. Display all employees who do not have any reportees 9. List employees who are having at least 2 reporting 10. List the department names which are having more than 5 employees 11. List department name having at-least 3 salesman 12. List employees from research and accounting having at-least 2 reporting 13. Display second max salary 14. Display 4th max salary 15. Display 5th max salary  -- Answer for nth Max Salary Co-Related Subqueries: 16. Write a query to get 4th max salary from EMP table 17. Wri
31 comments
Read more

Answers for SQL Functions

1. SQL> SELECT empno, ename FROM emp WHERE Length(ename) = 4; 2. SQL> SELECT empno, ename, job FROM emp where Length(job)=7; 3. SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual; 4. SQL>  SELECT empno, ename, job FROM emp WHERE Instr(job,'MAN') >0; 5. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job, 'MAN') =1; 6. SQL> SELECT empno, ename, job FROM emp WHERE (Length(ename) - Length(Replace(ename, 'L',''))) = 1; 7. SQL> SELECT * FROM dept WHERE Instr(dname,'O') > 0; 8. SQL> SELECT Concat(ename,' working as a ') || Concat(job, ' earns ') || Concat(sal, '  in ') || Conc at('dept ',deptno) AS text from emp; OR SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),'  in '),'dept '), deptno) AS text FROM emp; 9. SQL
40 comments
Read more