OraFAQ Forum: SQL & PL/SQL » query order by day

Home » SQL & PL/SQL » SQL & PL/SQL » query order by day

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query order by day [message #7518]

Thu, 19 June 2003 03:29

Sowmya
Messages: 5
Registered: August 2002

Junior Member

Hi,
I have output which have a date column and one pseudocolumn Day which gives the day name (monday,tuesday...) of that date column.
Now I want to sort the result according to day (NOT alphabetical sort). But result sorted with day starting with monday....

eg:
----
DAY
-----
Monday
Monday
Tuesday
Wednesday...and so on

Can this be possible with one single query?

Report message to a moderator

Re: query order by day [message #7523 is a reply to message #7518]

Thu, 19 June 2003 04:28

Maaher
Messages: 7065
Registered: December 2001

Senior Member

This might help:

  1  select ename
  2       , hiredate
  3       , to_char(hiredate,'DAY') day
  4       , to_char(hiredate,'D') day2
  5    from emp
  6*  order by to_char(hiredate,'D')
SQL> /

ENAME      HIREDATE  DAY       D
---------- --------- --------- -
WARD       22-FEB-81 SUNDAY    1
MARTIN     28-SEP-81 MONDAY    2
CLARK      09-JUN-81 TUESDAY   3
TURNER     08-SEP-81 TUESDAY   3
KING       17-NOV-81 TUESDAY   3
JONES      02-APR-81 THURSDAY  5
JAMES      03-DEC-81 THURSDAY  5
FORD       03-DEC-81 THURSDAY  5
SMITH      23-JAN-82 FRIDAY    6
ALLEN      20-FEB-81 FRIDAY    6
BLAKE      01-MAY-81 FRIDAY    6

ENAME      HIREDATE  DAY       D
---------- --------- --------- -
ADAMS      23-MAY-87 FRIDAY    6
SCOTT      19-APR-87 SATURDAY  7
MILLER     23-JAN-82 SATURDAY  7

14 rows selected.

HTH,
MHE

Report message to a moderator

Re: query order by day [message #7525 is a reply to message #7518]

Thu, 19 June 2003 05:34

Utham
Messages: 25
Registered: June 2003

Junior Member

u can refer to the following query

i hope it will work as ur need...

Consider a Table Test with 2 fields
x - Number
dt - Date

select x,dt,to_char(to_date(dt,'dd-mon-yyyy'),'Day') Dy from test order by to_char(to_date(dt,'dd-mon-yyyy'),'D')

let me its working or not.

rgds,
utham

Report message to a moderator

Re: query order by day [message #7537 is a reply to message #7523]

Thu, 19 June 2003 20:21

Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA

Senior Member

If you want to start with Monday, instead of Sunday, you will need some sort of modification, like the following:

SQL> SELECT ename,
  2         hiredate,
  3         TO_CHAR (hiredate, 'DAY') day,
  4         DECODE (TO_CHAR (hiredate, 'D') - 1, 0, 7, TO_CHAR (hiredate, 'D') - 1) day2
  5  FROM   emp
  6  ORDER BY DECODE (TO_CHAR (hiredate, 'D') - 1, 0, 7, TO_CHAR (hiredate, 'D') - 1)
  7  /

ENAME      HIREDATE  DAY             DAY2
---------- --------- --------- ----------
MARTIN     28-SEP-81 MONDAY             1
CLARK      09-JUN-81 TUESDAY            2
KING       17-NOV-81 TUESDAY            2
TURNER     08-SEP-81 TUESDAY            2
SMITH      17-DEC-80 WEDNESDAY          3
ADAMS      12-JAN-83 WEDNESDAY          3
JONES      02-APR-81 THURSDAY           4
FORD       03-DEC-81 THURSDAY           4
SCOTT      09-DEC-82 THURSDAY           4
JAMES      03-DEC-81 THURSDAY           4
ALLEN      20-FEB-81 FRIDAY             5
BLAKE      01-MAY-81 FRIDAY             5
MILLER     23-JAN-82 SATURDAY           6
WARD       22-FEB-81 SUNDAY             7

14 rows selected.

Report message to a moderator

Re: query order by day [message #7542 is a reply to message #7537]

Thu, 19 June 2003 22:46

Sowmya
Messages: 5
Registered: August 2002

Junior Member

Thanks Maheer, Utham and Barbara. Its working and the same way what i wanted.
But Barbara, can you pls explain me the decode function. I dint understand it.

Report message to a moderator

Re: query order by day - DECODE [message #7544 is a reply to message #7542]

Fri, 20 June 2003 00:14

Maaher
Messages: 7065
Registered: December 2001

Senior Member

The decode is interpreted as follows:

DECODE(to_char(date_field,'D')-1
       ,0 , 7
       ,to_char(date_field,'D')-1
       )
If to_char(date_field,'D')-1 equals 0, return 7
otherwise return to_char(date_field,'D')-1.

This mechanism makes that Monday gets value 1, Tuesday 2, etc... . In other words, using the Decode, you're week starts with Monday (value 1) instead of Sunday.

Decode in general works as follows:

DECODE(some_value
      ,v1, return1
      ,v2, return2
      ..
      ,vx, returnx
      ,default_return
      )

You evaluate some_value. If it is equal to v1, it returns return1, if it is equal to v2, it returns return2 etc... in the end, you can assign a default_return which is being returned by decode if no matches are found.

Follow the link for more info.

MHE

Report message to a moderator

Previous Topic:	How to take i/p parameter as SQL query
Next Topic:	Max function in oracle9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 13:24:55 CDT 2024