Re: SQL*plus please help

Messiah wrote:

> HI
>
> I am using oracle 8 with sql plus to return a query which will have the
> column headers school_name,child_name and dob.
> What I need to know is how to get the school_name grouped and the dob in
> order for the childs in that school.
> It's a order by within a group, below is my attempt so far:
>
> SELECT school.school_name,child.child_name,child.dob
> 2 FROM school,child
> 3 WHERE child.school_no = school.school_no and child.dob <= '01-feb-93'
> 4 ORDER BY school.school_name;
>
> This will get the school_name in order and together but the DOB in not in
> order.
>
> Any help recieved with great apprechation
>
> M
>
> --
>
> ----------------------------------------------------------------------------
> -----------
> This email and all attachments have been scanned
> with Norton Anti Virus, Total protection for Pc and Mac
>
> www.symantec.com

You are, in a SQL context, misusing the word GROUP ... grouping has an entirely different meaning and could lead to confusion when asking question. Grouping refers to aggregations with operators like MAX, SUM, and COUNT. What you are trying to do is strictly a sorting that is done by the ORDER BY clause.

Here's the solution if I understand the question:

SELECT s.school_name, c.child_name, c.dob FROM school s., child c
WHERE c.school_no = sl.school_no
AND c.dob <= TO_DATE('01-FEB-93')
ORDER BY school.school_name, child.dob;

I also took the liberty of aliasing the tables in your FROM clause with single letters to cut down on the amount of typing and make it easier to read as well as correcting your use of a date in the WHERE clause. You 'can' not use TO_DATE but it is highly inadvisable as Oracle does not guarantee you will get the correct result.

Daniel Morgan Received on Fri Jun 14 2002 - 10:22:34 CDT