Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Why doesn't this work?
Not true on two counts. 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.
--
Yoou 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 - 09:47:46 CST