| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Why doesn't this work?
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:
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
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. Received on Sun Dec 13 1998 - 11:13:01 CST
 |
 |