Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Why doesn't this work?

Re: Why doesn't this work?

From: KeyStroke (Jack L. Swayze Sr.) <KeyStrk_at_Feist_NO_SPAM_.Com>
Date: Sun, 13 Dec 1998 09:47:46 -0600
Message-ID: <3673E1A2.FC7E8E21@Feist_NO_SPAM_.Com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US