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
Pradeepa M R Gowda 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
Pradeepa M R Gowda 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
Pradeepa M R Gowda 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

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. Write a query to get 2nd…
21 comments
Read more

'Linux-x86_64 Error: 28: No space left on device' While trying to start the database -- Error

SQL> startup mount pfile='/tmp/initdlfasp12.ora'
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device


This as you can see is on Linux x86 with 64 bit processor. We got this error after we changed SGA on 10gR2 database. So was sure that this is something to do with the OS.

Parameters to check for this are shmall.

shmall is the total amount of shared memory, in pages, that the system can use at one time.

$cat /proc/sys/kernel/shmmax
53687091200

$ getconf PAGE_SIZE
4096

As per Oracle SHMALL should be set to the total amount of physical RAM divided by page size.

Our system has 64GB memory, so change kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216


Once this value is calculated you can modify Linux system configuration file directly.

$ su - root
vi /etc/sysctl.conf file:
kernel.shmall=16777216

and

# sysctl -p

Once this is done the database was started without any problem.
13 comments
Read more

Answers for SUB Queries

1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH');


2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));

3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST');

4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES');

5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES')

6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING'))

7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp)

8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not null)

9. select…
8 comments
Read more