Re: Oracle distinct select issue

From: <mastermind148_at_gmail.com>
Date: 6 Aug 2006 02:16:44 -0700
Message-ID: <1154855804.605011.121230@n13g2000cwa.googlegroups.com>

i tried it but it didn't work. even the more simplified select does not work

select

    distinct(inc.inc_cit_oid) searchcode,      inc.inc_id id
from

    servicedesk.itsm_incidents inc

it always dump all rows, not the distinct one's. but if the ID column is ommited everything goes well.

select

    distinct(inc.inc_cit_oid) searchcode, from

    servicedesk.itsm_incidents inc

any ideas?
Charles Hooper napísal(a):
> 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 Sun Aug 06 2006 - 04:16:44 CDT