Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Quiz
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
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...Received on Tue Oct 28 2003 - 08:38:44 CST
> 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.
> --------------------------------------------------------------------
-
>