Home » SQL & PL/SQL » SQL & PL/SQL » problem with order by
problem with order by [message #220819] Thu, 22 February 2007 04:55 Go to next message
mangeshkulk
Messages: 5
Registered: February 2007
Location: Pune
Junior Member
I have to select last_name,hire_date from employees table and have to arrange the records starting from first day as monday. How to arrange it using order by?
Re: problem with order by [message #220827 is a reply to message #220819] Thu, 22 February 2007 05:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Make sure that Monday is the first day of the week, because in a lot of NLS settings the first day of the week is Sunday. You can do that via the NLS parameter NLS_TERRITOY. In Germany, the first day of the week is Monday. You can alter the territory for your session through an alter session statement.
SQL> ALTER SESSION SET NLS_TERRITORY=GERMANY

Session altered.
Now, to order by the day of the week, you can use ORDER BY TO_CHAR(hire_date,'D).


MHE
Re: problem with order by [message #220828 is a reply to message #220819] Thu, 22 February 2007 05:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Sample Data?
Expected Output??

For ex: If you have 05-FEB-2007 , 06-FEB-2007 and 12-FEB-2007

Do you want the data as
05-FEB-2007 -Mon
12-FEB-2007 -Mon
06-FEB-2007 - Tue
??

Edit:
Got clarified after reading Maarten's reply. Cool

By
Vamsi

[Updated on: Thu, 22 February 2007 05:17]

Report message to a moderator

Re: problem with order by [message #220907 is a reply to message #220819] Thu, 22 February 2007 12:43 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
simply adjust the date as follows

if your week starts on sunday, simply

order by to_char(my_date - 1,'D');

No need for territory changes

[Updated on: Thu, 22 February 2007 12:44]

Report message to a moderator

Re: problem with order by [message #220918 is a reply to message #220819] Thu, 22 February 2007 13:52 Go to previous messageGo to next message
IT Guru
Messages: 59
Registered: January 2007
Member
I have to select last_name,hire_date from employees table and have to arrange the records starting from first day as monday. How to arrange it using order by?

SELECT last_name,hire_date
FROM employyes
ORDER BY TO_CHAR(hire_date - 1 ,'D')


& best of luck for your 10Z-007 exam.

Re: problem with order by [message #220968 is a reply to message #220819] Thu, 22 February 2007 22:46 Go to previous messageGo to next message
mangeshkulk
Messages: 5
Registered: February 2007
Location: Pune
Junior Member
Thanks All for help.

-Mangesh
Re: problem with order by [message #220979 is a reply to message #220907] Fri, 23 February 2007 00:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Bill B wrote on Thu, 22 February 2007 19:43
simply adjust the date as follows

if your week starts on sunday, simply

order by to_char(my_date - 1,'D');

No need for territory changes

This query only works if your nls settings are known in advance. It will not work in a german-settings database..
Re: problem with order by [message #221002 is a reply to message #220907] Fri, 23 February 2007 02:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
edit: Frank said the same thing.

[Updated on: Fri, 23 February 2007 02:41]

Report message to a moderator

Re: problem with order by [message #221045 is a reply to message #220979] Fri, 23 February 2007 07:47 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Frank wrote on Fri, 23 February 2007 00:07
Bill B wrote on Thu, 22 February 2007 19:43
simply adjust the date as follows

if your week starts on sunday, simply

order by to_char(my_date - 1,'D');

No need for territory changes

This query only works if your nls settings are known in advance. It will not work in a german-settings database..


I never said use american numbering, I said adjust the query so that Monday will collate first instead of sunday, like he asked.
Re: problem with order by [message #221056 is a reply to message #221045] Fri, 23 February 2007 09:02 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But without explicitly setting the nls data, this would only work on a database with a given setting. For applications that should work worldwide, in a variety of databases, regardless of nls-settings, this will not work.
Previous Topic: sql result arrangement
Next Topic: Comparing two tables with no matching keys in PL/SQL
Goto Forum:
  


Current Time: Sat Dec 03 01:13:46 CST 2016

Total time taken to generate the page: 0.07965 seconds