group by

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 Received on Mon Jun 19 2006 - 09:04:38 CDT