Re: Quiz

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 28 Oct 2003 16:16:57 -0000
Message-ID: <3f9e967b$0$251$ed9e5944@reading.news.pipex.net>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bnlv50$4ev$1$8302bc10_at_news.demon.co.uk...
>
>
> select 'Colorado' from dual;
>
> seems to fit the terms of your question 4
> perfectly - but I'm sure that wasn't what
> you intended.
>
> In passing, the solution you posted changed
> the question from 'location name' to 'location'
> and returned the numeric.
>
> Were you expecting vanilla SQL, or would
> an Oracle analytic solution be accepted for
> the more serious solution ? After all, it does
> do a single pass across the data, and is
> often a less I/O and CPU intensive operation.
>
> Off the top of my head, the following looks
> about right:
>
> select v.loc_name
> from (
> select
> rank() over(order by location desc) rnk,
> loc_name
> from
> test
> ) v
> where rnk = 2
> ;
>
>
> Of course, that leaves room for agument about
> what exactly you intended to happen for ties.

And whether the students should know what to do in environments that run unsupported or non-enterprise versions of Oracle, since analytical functions are *supposed* not to be available in std edition.

in your example this point is moot at least for 9.2

SQL> select banner from v$version;

BANNER

Oracle9i Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production

SQL> select v.loc_name
2 from (

  3      select
  4          rank() over(order by location desc) rnk,
  5          loc_name
  6      from
  7          test
  8      ) v

9 where rnk = 2
10 ;

LOC_NAME

Colorado

I see that DBMS_STATS uses analytical functions now to gather stats so this is presumably why at least some analytics exist in std edition.

-- 
Niall Litchfield
Oracle DBA
Audit Commission Uk

Received on Tue Oct 28 2003 - 10:16:57 CST