Re: group by

"Jeff Kish" <jeff.kish_at_mro.com> a écrit dans le message de news: ivad92l4oemnu8nffh3dbeeu9m6t1pu9rd_at_4ax.com...
| Hi.
|
| I have read lots, but obviously not the right stuff.
| A reference pointer would be fine, or an answer.
|
|
| I'd like to use sql to find out:
|
| given a metadata table
| where object is a table name
| attribute is a col name,
| keyseq is if the attribute is
| part of a primary key
|
| AND
|
| given (by rules, not db design) that you should only have a single attribute
| with a given non null value in keyseq
|
| AND
|
| table metatable(object varchar2, attribute varchar2, keyseq int)
| table1, col1, 1
| table1, col2, 2
| table1, col3, null
| table1, col4, null
| table2, col1, 1
| table2, col2, 1
| table2, col3, null
| table2, col4, null
| table3, col1, 1
| table3, col2, 2
| table3, col3, null
| table3, col4, null
|
|
| I'd like to easily find out what tables have more than one attribute for any
| keyseq that is not null.
|
|
| Is it possible?
|
| I started off with select object, attribute, keyseq from metatable
| where keyseq is not null, and got a few thousand rows.
|
| Then I tried a count(*) and group by (I wish I knew this aggregate stuff
| better) like this:
|
| select object, keyseq, count(keyseq) from metatable
| where keyseq is not null
| group by object, keycolseq
| order by (count(keyseq))
|
| and got a few thousand rows, where the very last row told me the object that
| had more than one attribute with a single keyseq value.
|
| So yes, I can figure it out, but I was hoping/wondering if there was a better
| way, using just sql, that would return what object/attribute had the
| 'violation'.
|
| I thought I might be able to use the above query in some sort of
| subquery/exists combination, but I'm at a loss.
|
|
| thanks for your time and knowledge.
|
|
|
|
|
| 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 Received on Mon Jun 19 2006 - 09:40:34 CDT