Re: Why doesn't this work?

You are obviously quite emotional about this. I frequently get this reaction from people when I point out a flaw in their 'sacred cow' software. The flaw is that Oracle doesn't allow ORDER BY in views. There is no reason for that other than philosophical arrogance. The purpose for using database software in business is to make money, not to 'preach' "Relational Purity" to the masses. So, 'Mr. From Oracle, but your views don't reflect Oracle' - FIX THE SOFTWARE.

As to my having an error in the syntax, I would admit that. As I don't have a working version of Oracle on my home PC (Oracle Lite is such a CROCK! it is still way, way to hard to install and make work - things on a PC should be SIMPLE), and as I didn't take the time to dial into work to check my syntax, I will admit I made a coding error.

As to GROUP BY not equaling ORDER BY - that is obvious. What YOU (Mr. ARROGANT) fail to realize is that I CAN make GROUP BY act like an ORDER BY as long as there is a primary key (of reasonable number of columns) on the table.

BACK OFF - ARROGANT! - and think about helping people solve their problems - instead of being so insecure to wrap your intelligence up with childish arrogance. REMEMBER! we, your CUSTOMERS are doing YOU a favor by buying and using your software. YOU are not doing us a favor by providing it - so STOP ACTING AS IF THE WORLD NEEDS TO BOW DOWN TO THE GOD OR ORACLE. Thomas Kyte wrote:
>
> A copy of this was sent to "KeyStroke (Jack L. Swayze Sr.)" <KeyStrk_at_Feist.Com>
>
> On Sun, 13 Dec 1998 09:47:46 -0600, you wrote:
>
> >Not true on two counts.
>
> what do you mean Not true. It is true that count(*) must be aliased and I did
> not comment on that, however it is 100% true that:
>
> -- VIEWS cannot have ORDER BYs.
> -- GROUP BY != ORDER BY
> -- Your view doesn't work, the way you did it won't work since
>
> 1 create view v as
> 2 select ename, cnt
> 3 from
> 4 (select ename, count(*) cnt
> 5 from emp
> 6 group by ename
> 7 )
> 8* group by cnt
> SQL> /
> select ename, cnt
> *
> ERROR at line 2:
> ORA-00979: not a GROUP BY expression
>
> so you would have to add in ename as well (group by cnt, ename). The
> performance of such as thing is pretty bad (you have to sort by the ENTIRE
> record in the view not just the cnt field), and since you have to sort by the
> entire field, you may quickly hit:
>
> ERROR:
> ORA-01467: sort key too long
>
> since you have to sort lots more fields then the order by clause would have had.
>
> Also, in general GROUP BY is NOT even close to ORDER BY.
>
> Lets say the example did not have an aggregate in the view definition. lets say
> instead the view they were trying to create was:
>
> create view v
> as select job, sal from emp
> order by sal;
>
> Consider then:
>
> SQL> select job, sal from emp
> 2 order by sal;
>
> JOB SAL
> --------- ----------
> CLERK 800
> CLERK 950
> PRESIDENT 1000
> CLERK 1100
> SALESMAN 1250
> SALESMAN 1250
> CLERK 1400
> SALESMAN 1500
> SALESMAN 1600
> MANAGER 2850
> MANAGER 2975
> MANAGER 3000
> ANALYST 3000
> ANALYST 3000
>
> 14 rows selected.
>
> SQL> select job, sal from emp group by sal, job;
>
> JOB SAL
> --------- ----------
> CLERK 800
> CLERK 950
> PRESIDENT 1000
> CLERK 1100
> SALESMAN 1250
> CLERK 1400
> SALESMAN 1500
> SALESMAN 1600
> MANAGER 2850
> MANAGER 2975
> ANALYST 3000
> MANAGER 3000
>
> 12 rows selected.
>
> GROUP BY has a tendency to GROUP things -- remove duplicates, the above 2
> queries are NOT the same, return different rows. Granted, in this one
> particular example, name was already uniqued in the first group by so no rows
> would be removed, however, in general -- rows can and will be silently removed,
> changing the result set.
>
> Lastly, order by X desc is really hard to do with group by (especially on
> character fields). You can do it with numbers by "group by -x" but then you
> have to "select -x" as well and the person using the view would have to know
> they need to "select -x" from the view to undo your negation.
>
> Looking at your reasoning below, "what if you bought a canned application and
> the application doesn't present the data the way you want....." If you take a
> canned application and rewrite its views as you describe above -- you will most
> likely BREAK the application. 1 -- adding the group by makes the view
> non-updateable and it might need to be and 2 -- you change the results of the
> view by grouping.
>
> GROUP BY != ORDER BY for sorting either.
>
> Group by MIGHT NOT ALWAYS sort. There is nothing anyway saying that it must and
> .... with partitions and parallel query (consider if the data is partitioned on
> the values you group by -- parallel query will kick in and tend to parallelize
> by partition. Each parition will sort itself but since they are non-overlapping
> -- they can be merged in any order. Partitions and parallel query can give you
> non-ordered group bys).
>
> >1) one reason the CREATE VIEW doesn't work is that the
> >COUNT(NAME) has to be given an alias. 2) you can accomplish the same thing as
> >an ORDER BY by using GROUP BY. This means that you don't have to make the
> >application apply the ORDER BY clause. Please refer to the discussion in this
> >newsgroup labeled 'make ORDER BY in a view'. There may be reasons technical,
> >political, or economic which restrict changing the original SELECT statement and
> >putting an ORDER BY clause on it. What if you bought a 'canned' application and
> >the application doesn't present the data to you in the fashion you wish? You
> >don't have the source code, so you cant change the original SELECT statement.
> >Therefore it would be much more useful if the DBMS allowed and ORDER BY claus on
> >the view, but since it doesn't, you can accomplish the same thing this way.
> >
> >PMG try this:
> >
> >create view v as
> >select name, name_cnt from
> >(select name_cnt, name, count(*) no_info from
> > (select name, count(name) name_cnt
> > from emp
> > group by name
> > )
> > group by name_cnt
> >);
> >
> >Thomas Kyte wrote:
> >>
> >> A copy of this was sent to PMG <pete_g_at_2xtreme.net>
> >> (if that email address didn't require changing)
> >> On Sun, 13 Dec 1998 06:35:33 GMT, you wrote:
> >>
> >> >This query works in SQLPlus:
> >> >
> >> >select name, count(name)
> >> >from emp
> >> >group by name
> >> >order by count(name) desc;
> >> >
> >> >But why doesn't this work:
> >> >
> >> >create view v as
> >> >select name, count(name)
> >> >from emp
> >> >group by name
> >> >order by count(name) desc;
> >> >
> >> >I thought views were just stored queries. So if the first one works why
> >> >doesn't the second work?
> >>
> >> views are not just stored queries. A view is much more like a database table
> >> then a stored query. Since tables and relational sets in general, do not have
> >> ORDER, order by not only doesn't make sense on them -- it is not allowed in the
> >> language.
> >>
> >> In order for a client applicatoin using a SELECT to be gauranteed to get the
> >> data in sorted order -- that application must apply the order by clause.
> >>
> >> Thomas Kyte
> >> tkyte_at_us.oracle.com
> >> Oracle Service Industries
> >> Reston, VA USA
> >>
> >> --
> >> http://govt.us.oracle.com/ -- downloadable utilities
> >>
> >> ----------------------------------------------------------------------------
> >> Opinions are mine and do not necessarily reflect those of Oracle Corporation
> >>
> >> Anti-Anti Spam Msg: if you want an answer emailed to you,
> >> you have to make it easy to get email to you. Any bounced
> >> email will be treated the same way i treat SPAM-- I delete it.
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.

--
You can email me by removing the _NO_SPAM_ in the email address. Make sure you remove all three underscores. Received on Sun Dec 13 1998 - 12:11:53 CST