scrub the comments about analytics. analytical functions != OLAP.
mea culpa
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"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.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
> ____Belgium__November (EOUG event - "Troubleshooting")
> ____UK_______December (UKOUG conference - "CBO")
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1067035247.574203_at_yasure...
> > 4. Assuming the following table and data ...
> >
> > CREATE TABLE test (
> > location NUMBER(2),
> > loc_name VARCHAR2(20));
> >
> > INSERT INTO test VALUES (1, 'Washington');
> > INSERT INTO test VALUES (2, 'Oregon');
> > INSERT INTO test VALUES (3, 'California');
> > INSERT INTO test VALUES (4, 'Idaho');
> > INSERT INTO test VALUES (5, 'Colorado');
> > INSERT INTO test VALUES (6, 'Vermont');
> > COMMIT;
> >
> > Write a single SQL statement that will return the location name
> > that has the second highest location number.
> > --------------------------------------------------------------------
> -
> >
>
>
>
Received on Tue Oct 28 2003 - 10:19:46 CST
