Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: View and group by
A copy of this was sent to "Michal Piasecki" <michal.piasecki_at_cr-media.com.pl>
(if that email address didn't require changing)
On Mon, 13 Dec 1999 17:33:09 GMT, you wrote:
>I have no Idea how to create view and keep all relations in my query
>I need to keep order in my result by the value of NM field so:
>
>CREATE VIEW "MICHAL".SOMEVIEW ( C1, C2 )
^^^^^^^^^^ name the columns from the select, theyare BOTH NM - that is the problem. has nothing to do with group by...
>AS
>SELECT BRANZE.NM, PODBRANZE.NM
>FROM BRANZE, PODBRANZE
>WHERE (BRANZE.IDBRANZY=PODBRANZE.IDBRANZY)
>GROUP BY BRANZE.NM, PODBRANZE.NM
>
>Line 2 error - duplicate name of the column.
>
>- there's written in the Oracle8 Complete Reference that
>even if there are no group functions in the select clause
>, the group by can still consolidate rows so it generally
>should work as order but it's not. What can I do in
>that case?
beware == group by <> order by. for example:
SQL> create table emp as select * from scott.emp where rownum <= 5; Table created.
SQL> create index emp_idx on emp(job,ename); Index created.
SQL> select ename, job from emp where ename >chr(0) and job >chr(0) 2 group by ename, job ;
ENAME JOB
---------- ---------
smith CLERK jones MANAGER allen SALESMAN martin SALESMAN ward SALESMAN
indexes, particular options, optimizer mode, NLS settings, many things can make it so group by <> order by as above.
>
>
>I appreciate any help.
>_________________________________________________
>Michal Piasecki
>CR Media
>tel: (+48)(022)690-68-62
>e:mail - michal.piasecki_at_cr-media.com
>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 13 1999 - 12:09:11 CST