Re: group by problem

From: Scott Mattes <Scott_at_TheMattesFamily.ws>
Date: Wed, 30 Apr 2003 12:38:36 GMT
Message-ID: <gtPra.8991$Jf.4341560@news1.news.adelphia.net>

Depending on your level of Oracle, this is probably a job for analytical functions.

Something like

  select tablename.*,

           count(*) over
             (partition by node
              order by node ) anzahl

  from ams.hardware
where anzahl > 1;

"Marc Eggenberger" <marc.eggenberger_at_itc.alstom.com> wrote in message news:MPG.1919b80bb3de5d0d989684_at_H02374...
> Hi there.
>
> I have a question to a sql problem.
> I have a two tables, one is called hardware, the other is called
> location.
>
> in hardware I have several columns where one is called node which
> contains the MAC-address of a host.
>
> I now have to get a list of all the hosts which have a MAC-address which
> is used more than once ...
>
> If I do a
>
> select * from (
> select node, count(node) as anzahl from ams.hardware group by NODE
> )
> where anzahl > 1
>
> I get all the MAC addresses which are used more than once ... but I need
> the hostsnames and other information out of it .. I tried:
>
> select * from (
> select h.NODE, h.HOSTNAME, l.ORT, count(h.node) as anzahl
> from ams.hardware h, ams.LOCATION l
> group by h.node
> where h.LOC_RECNO = l.STANDORT_NR (+)
> )
> where anzahl > 1
>
> but this does not work .. it says that the right ) is missing ....
>
> I need hardware.Node, hardware.Hostname, location.Ort from all Nodes
> which have an MAC-Address in hardware.Node which occurs more than once
> in the table.
>
> can someone help me how to achieve this?
>
> thanks
>
>
> --
> mfg
> Marc Eggenberger
Received on Wed Apr 30 2003 - 07:38:36 CDT