Re: Quiz

From: VC <boston103_at_hotmail.com>
Date: Tue, 28 Oct 2003 23:19:47 GMT
Message-ID: <mQCnb.53235$HS4.240908@attbi_s01>

Hello Daniel,

The answers are for the task as stated (unique 'location'):

1. If you do not fancy analytics, then the Oracle 'rownum' idiom would be as performant:

select * from (
  select rownum rn, t1.* from
    (select * from test order by location desc) t1
) where rn =2

2. If one wishes to be SQL'92, compliant then

select * from test t1 where 2=(select count(*) from test where location >= t1.location)

is one way ...

select * from (
  select t1.*, (select count(*) from test where location >= t1.location) rn from test t1
) where rn=2

.. is another

select t1.loc_name from test t1, test t2 where t2.location > = t1.location group by t1.loc_name having count(*)=2;

... yet another.

Rgds.

  "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1067378174.678766_at_yasure...   Jonathan Lewis wrote:

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.
--------------------------------------------------------------------
    
-
  
  I accept all answers that are general enough to return the correct answer. Especially when the 
  quiz is given to students at their fourth class. They are very aggressive and motivated learners 
  but not so much as to have discovered your solution yet.

  I'll post it to the class web site for those not lurking here.

  Thanks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Oct 28 2003 - 17:19:47 CST