Re: How to write a query for this?

From: amana32 <dontmailme_at_hotmail.com>
Date: Fri, 15 Dec 2000 04:04:57 GMT
Message-ID: <JLg 5.313$yd7.25171@bgtnsc06-news.ops.worldnet.att.net>

Nice query.

    Jay Weiland wrote in message <3A3968AE.C3DF02C8_at_pixar.com>...     Hey Gad,

         Magical in-line views are the answer!

    select m1.emp id, m1.location, m1.info     from my table m1,

          (select   min(decode(location, 'FO', 1, 'HQ', 2)) score, 
                    emp id 
           from     my table 
           group by emp id) rank 

    where m1.emp id = rank.emp id
    and decode(m1.location, 'FO', 1, 'HQ', 2) = rank.score;

    ...and this particular snippet of code is easily updateable if you ever get a third location.

    Jay!!!

    Gad Krosner wrote:

        Hi There! 
        I have a table MYTABLE with columns EMPID and LOCATION (and 
other 
        columns). 
        LOCATION may be "HQ" or "FO" and one EMPID may have both HQ and 

FO.

        I want to retrieve data but not more than one row per EMPID. If an

        EMPID has both HQ and FO, I want to retrieve only the row with FO.

        How do I write that query?

        Thanks.

        Sent via Deja.com 
        http://www.deja.com/

--

Received on Thu Dec 14 2000 - 22:04:57 CST