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.
2. Statement from Picture2.
3. Solution using DECODE Function.
4. Both CASE and DECODE functions can be used directly in ORDER BY, check the statements below.
CASE:
DECODE:
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;
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
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;
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);
FROM emp
ORDER BY DECODE(to_char(hiredate,'fmDay'),
'Monday',1,
'Tuesday',2,
'Wednesday',3,
'Thursday',4,
'Friday',5,
'Saturday',6,
'Sunday',7);
Comments
what if we use order by 3 in case method.
well thanks for the example and please do reply.
Please guide me what i have to do.
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.....
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
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';
FROM EMP A,EMP B
WHERE A.MGR=B.EMPNO
AND A.SAL>B.SAL
Grate create view to scott;
Then login as scott and create view