Re: group by

On Mon, 19 Jun 2006 16:40:34 +0200, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
>"Jeff Kish" <jeff.kish_at_mro.com> a écrit dans le message de news: ivad92l4oemnu8nffh3dbeeu9m6t1pu9rd_at_4ax.com...
>| Hi.

<snip>
>|
>| Thanks
>| Jeff Kish
>
>You're almost at the solution:
>
>select object, keyseq, count(keyseq)
>from metatable
>where keyseq is not null
>group by object, keyseq
>having count(*) > 1 ---<----- just this line to add
>order by count(keyseq)
>/
>
>Now if you also want the attribute name then:
>
>with data as (
> select object, attribut, keyseq,
> count(*) over(partition by object, keyseq) cnt
> from metatable
> where keyseq is not null )
>select object, attribute, keyseq
>from data
>where cnt > 1
>order by 1, 3, 2
>/
>
>Regards
>Michel Cadot
>

Thanks Michel.
I'm having a bit of trouble with the second part (with data as) ?
The first part using the 'having count' is just fine.

Can you illuminate it for me? I don't see a 'with data as' statement in the oracle help, and I'm not sure how to put it all together, though I do see alot of examples with the partition syntax.

Regards
Jeff Kish Received on Mon Jun 19 2006 - 11:13:56 CDT