Create a query that display the last name,hire date and the day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday.LAST_NAME HIRE_DATE DAYGrant 24-MAY-99 MONDAYErnst 21-MAY-99 TUESDAYMourgos 16-NOV-99 TUESDAY------- ----- SUNDAY

Questions by zecar   answers by zecar

Showing Answers 1 - 11 of 11 Answers

Deepali

  • Sep 14th, 2006
 

try this query-

SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAY

FROM employees

ORDER BY hire_date, DAY;

if we order by hire date it will give the dates in ascending order which is fine but if we order by day then it will give the days in ascending order like monday,friday,saturday,sunday,thurday.... (alphaebetically)it wont give monday,tueday,wednesday

  Was this answer useful?  Yes

Manikandan

  • Sep 22nd, 2006
 

SELECT last_name, hire_date, TO_CHAR(hire_date,'DAY') AS DAYFROM employeesORDER BY DAY;

  Was this answer useful?  Yes

amikat

  • Jun 3rd, 2008
 

select last_name,hire_date, to_char(hire_date,'DAY') as DAY
 from emp
order by to_char(hire_date-1,'d');

  Was this answer useful?  Yes

NANDU@NQP3>select dayofweek Day from test456;

DAY
--------------------
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

7 rows selected.

NANDU@NQP3>select dayofweek day, decode(dayofweek , 'Monday'    ,     1
  2                                      , 'Tuesday'   ,     2
  3                                      , 'Wednesday' ,     3
  4                                      , 'Thursday'  ,     4
  5                                      , 'Friday'    ,     5
  6                                      , 'Saturday'  ,     6
  7                                      , 'Sunday'    ,     7) DISCARD_STUFF
  8  from test456 order by 2;

DAY                  DISCARD_STUFF
-------------------- -------------
Monday                           1
Tuesday                          2
Wednesday                        3
Thursday                         4
Friday                           5
Saturday                         6
Sunday                           7

7 rows selected.

NANDU@NQP3>

With Oracle there are 100's of ways of doing things :). Hope this is the way you wanted the output to be displayed.


in continuation, you can discard the discard_stuff and get the order wanted as shown below.

select dayofweek day
from test456
order by decode(dayofweek , 'Monday' , 1
, 'Tuesday' , 2
, 'Wednesday' , 3
, 'Thursday' , 4
, 'Friday' , 5
, 'Saturday' , 6
, 'Sunday' , 7)

--Sagi


--Sagi

  Was this answer useful?  Yes

emraan

  • Dec 28th, 2009
 

SELECT ename, hiredate, to_char(hiredate, 'DAY') FROM emp ORDER BY to_char(hiredate-1, 'd') 

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions