Re: does a table always need a PK?
Date: Wed, 3 Sep 2003 13:59:21 -0700
Message-ID: <4Ls5b.22$eu1.271_at_news.oracle.com>
"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
news:s9s5b.19$eu1.271_at_news.oracle.com...
> "Christopher Browne" <cbbrowne_at_acm.org> wrote in message
> news:bj5gqk$fkobb$1_at_ID-125932.news.uni-berlin.de...
> > > MEDIAN is redundant:
> > >
> > > select sal from (
> > > select rownum rn, sal from (
> > > select sal from emp order by sal
> > > )
> > > ), (select max(rownum) rowcount from emp)
> > > where rn = (rowcount+1)/2
> > > union
> > > select sum(sal)/2 from (
> > > select rownum rn, sal from (
> > > select sal from emp order by sal
> > > )
> > > ), (select max(rownum) rowcount from emp)
> > > where mod(rowcount,2)=0 and (rn = rowcount/2 or rn = rowcount/2 + 1)
> >
> > What is "rownum?"
> >
> > I am quite sure that's not standard SQL.
>
> OK. How about this one?
>
> select sal from (
> select sal, row_number() over (order by sal) rn,
> sum(1) over () rowcount from emp
> ) where rn = (rowcount+1)/2
> union
> select sum(sal)/2 from (
> select sal, row_number() over (order by sal) rn,
> sum(1) over () rowcount from emp
> ) where mod(rowcount,2)=0 and (rn = rowcount/2 or rn = rowcount/2 + 1)
If analytic functions are still considered not standard sql, use
select
sal,
(select count(1) from emp ee where ee.sal < e.sal) rn,
(select count(1) from emp) rowcount
from emp e
in inner subquery. (There is a subtle problem that numbering is no longer uniquie, so that outer query should be amended accordingly). Received on Wed Sep 03 2003 - 22:59:21 CEST