Re: Oracle Newbie - SQL question

"Jack Straw" <jkstraw_no_spam__at_hotmail.com> wrote in message news:%aiZ8.2493$95.40166_at_news...
> For example I read the statement as follows:
>
> ORDER by TO_CHAR(hire_date -1,'d')
>
> ORDER [the results] BY
> TO_CHAR [convert from number to character]
> (hire_date -1 [subtract 1 day from the hire date], 'd'[no clude what this
> does?]
>
> Questions:
>
> 1. What does the 'd' represent?

It is the date fromat model for the to_char function. You can find out more about this at
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a85397/function.htm#79294

> 2. I have 20 rows of data with all different hire dates....how does
> subtracting 1 day from all the hire dates cause all rows to be ordered
> Monday -> Sunday in the query result?

Sybrand gave you the answer. What the order by clause is saying is order by the daynumber of the day before the value in the hiredate column.

So for every Monday the previous day is Sunday. These therefore come first. Similarly for evry row which has a hiredate of sunday (not many one would hope) the previous day (the order by clause) is Saturday so these come last.

If my explanation is too wordy for you issue the following query which should make things clearer by showing you the value that is being used to order.

SELECT last_name, hire_date,
TO_CHAR(hire_date,'fmDay'),TO_CHAR(hire_date -1,'d') ORDER_BY_VALUE FROM employees
ORDER by TO_CHAR(hire_date -1,'d')"

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************