Re: Oracle distinct select issue

mastermind148_at_gmail.com wrote:
> Hello.
> I have one problem with oracle select. Let have this simplified select
> select * from
> (
> select
> itms.CIT_SEARCHCODE searchcode,
> inc.INC_ID id
> from
> servicedesk.itsm_incidents inc,
> servicedesk.ITSM_CONFIGURATION_ITEMS itms
> where
> (inc.INC_CIT_OID = itms.CIT_OID)
> ORDER BY inc.INC_ID DESC
> )
> where ROWNUM<=20
>
> This select dumps 20 rows. But, I want to select 20 rows with unique
> searchcode. So i tried something like this
> select distinct(searchcode),id from
> (
> select
> itms.CIT_SEARCHCODE searchcode,
> inc.INC_ID id
> from
> servicedesk.itsm_incidents inc,
> servicedesk.ITSM_CONFIGURATION_ITEMS itms
> where
> (inc.INC_CIT_OID = itms.CIT_OID)
> ORDER BY inc.INC_ID DESC
> )
> where ROWNUM<=20
> But i didnt work. Select dumps 20 rows but without distinct searchcode.
> then i tried to select just the searchcode column without id.
> select distinct(searchcode) from
> (
> select
> itms.CIT_SEARCHCODE searchcode,
> inc.INC_ID id
> from
> servicedesk.itsm_incidents inc,
> servicedesk.ITSM_CONFIGURATION_ITEMS itms
> where
> (inc.INC_CIT_OID = itms.CIT_OID)
> ORDER BY inc.INC_ID DESC
> )
> where ROWNUM<=20
> this select worked properly. It selects 20 distinct searchcodes.
> Have anybody any idea how to select 20 rows from that joined tables, by
> unique searchode?
> I will appreciate any help. I got stuck :)

Try something like this:
SELECT
  SEARCHCODE,
  ID
FROM
  (SELECT DISTINCT
    ITMS.CIT_SEARCHCODE SEARCHCODE,
    INC.INC_ID ID
  FROM
    SERVICEDESK.ITSM_INCIDENTS INC,
    SERVICEDESK.ITSM_CONFIGURATION_ITEMS ITMS   WHERE
    INC.INC_CIT_OID = ITMS.CIT_OID
  ORDER BY
    INC.INC_ID DESC,
    ITMS.CIT_SEARCHCODE)
WHERE
  ROWNUM<=20;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Aug 05 2006 - 17:52:32 CDT