Re: How to write a query for this?

From: Jay Weiland <jay_at_pixar.com>
Date: Thu, 14 Dec 2000 16:41:18 -0800
Message-ID: <3A3968AE.C3DF02C8@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 - 18:41:18 CST