Re: Oracle distinct select issue

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 6 Aug 2006 07:00:24 -0700
Message-ID: <1154872824.286899.203470@p79g2000cwp.googlegroups.com>

mastermind148_at_gmail.com wrote:
> 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.
> 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?

I have a feeling that your goal for the SQL statement is not as clear as it should be. You are looking for 20 unique/distinctly different ITMS.CIT_SEARCHCODE values, but you also want to include the associated INC.INC_ID values. Assume that your data looks like this:

ITMS.CIT_SEARCHCODE     INC.INC_ID
AAAA                                  111
BBBB                                  121
BBBB                                  111
BBBB                                  121
CCCC                                  131
DDDD                                  131

If you use a SELECT DISTINCT with the above data set, you will only eliminate one of the BBBB,121 result rows. The output of the SELECT DISTINCT query will look like this (the two BBBB rows may reverse order on some versions of Oracle that perform implicit sorting for DISTINCT and GROUP BY - without an ORDER BY clause, the rows returned are in essentially random order.):

ITMS.CIT_SEARCHCODE     INC.INC_ID
AAAA                                  111
BBBB                                  121
BBBB                                  111
CCCC                                  131
DDDD                                  131

As you can see from the above, you have a repetition in the ITMS.CIT_SEARCHCODE column, which is apparently not what you want. Try switching to GROUP BY on the ITMS.CIT_SEARCHCODE column, and you will be certain to not have any duplicates in that column, but then you need to decide what to do with the INC.INC_ID column - you may need to cheat by using MIN or MAX for that column.

SELECT
  SEARCHCODE,
  ID
FROM
   (SELECT

     ITMS.CIT_SEARCHCODE SEARCHCODE,
     MAX(INC.INC_ID) ID

   FROM
    SERVICEDESK.ITSM_INCIDENTS INC,
    SERVICEDESK.ITSM_CONFIGURATION_ITEMS ITMS   WHERE
    INC.INC_CIT_OID = ITMS.CIT_OID
  GROUP BY
    ITMS.CIT_SEARCHCODE
  ORDER BY
    ITMS.CIT_SEARCHCODE)
WHERE
  ROWNUM<=20;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Aug 06 2006 - 09:00:24 CDT