469,148 Members | 1,271 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

group by

Hi.

I have read lots, but obviously not the right stuff.
A reference pointer would be fine, or an answer. I in fact got an oracle
solution that I am still researching, but was wondering about either a generic
or an ms sql server solution?
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, and which attribute it was with the problem.
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.

Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more advanced
solution I'm not showing because I don't understand it, and it might be oracle
specific.

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)

thanks for your time and knowledge.

Thanks
Jeff Kish
Jeff Kish
Jun 19 '06 #1
7 1868
This should work. WHERE limits which cases go into the tally.
HAVING limits which results are output after the tally is completed.

In this case, it limits the output to those object-keyseq combinations
where the count is greater than one, which I believe is what you're
after.
Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more advanced
solution I'm not showing because I don't understand it, and it might be oracle
specific.

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)


Jun 19 '06 #2
Jeff Kish (je*******@mro.com) writes:
Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more
advanced solution I'm not showing because I don't understand it, and it
might be oracle specific.

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)


It can't be any more standard SQL than this. The above SELECT should
run on any RDBMS that supports SQL.

HAVING is like WHERE, but is applied after the GROUP BY, and thus
permits filters with aggregate functions.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 19 '06 #3
On Mon, 19 Jun 2006 21:58:38 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:
Jeff Kish (je*******@mro.com) writes:
Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more
advanced solution I'm not showing because I don't understand it, and it
might be oracle specific.

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)


It can't be any more standard SQL than this. The above SELECT should
run on any RDBMS that supports SQL.

HAVING is like WHERE, but is applied after the GROUP BY, and thus
permits filters with aggregate functions.

How would I add the attribute column so I know exactly which ones are
of interest? That was what is the most difficult thing for me to
figure out.

Thanks
Jun 20 '06 #4
Jeff Kish (ki*******@charter.net) writes:
How would I add the attribute column so I know exactly which ones are
of interest? That was what is the most difficult thing for me to
figure out.


Not sure that I understood your original question, but try:

SELECT a.object, a.attribute, a.keyseq
FROM metatable a
JOIN (SELECT object, keyseq
FROM metatable
WHERE keyseq IS NOT NULL
GROUP BY object, keyseq
HAVING COUNT(*) > 1) AS b ON a.object = b.object
AND a.keyseq = b.keyseq

The thing in parenthesis is a derived table. You could think of a
derived table as a temp table within the query, but never materialised.
Or even computed, the optimizer may recasts the computation order
to get performance. This is a great tool to write complex queries
effeciently.

And, yes, this is ANSI-SQL that should run on most RDBMS. (It's not
as vintage as HAVING though.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 20 '06 #5
On Tue, 20 Jun 2006 22:04:41 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:
Jeff Kish (ki*******@charter.net) writes:
How would I add the attribute column so I know exactly which ones are
of interest? That was what is the most difficult thing for me to
figure out.


Not sure that I understood your original question, but try:

SELECT a.object, a.attribute, a.keyseq
FROM metatable a
JOIN (SELECT object, keyseq
FROM metatable
WHERE keyseq IS NOT NULL
GROUP BY object, keyseq
HAVING COUNT(*) > 1) AS b ON a.object = b.object
AND a.keyseq = b.keyseq

The thing in parenthesis is a derived table. You could think of a
derived table as a temp table within the query, but never materialised.
Or even computed, the optimizer may recasts the computation order
to get performance. This is a great tool to write complex queries
effeciently.

And, yes, this is ANSI-SQL that should run on most RDBMS. (It's not
as vintage as HAVING though.)

Wow.
I'll have to study up some more on the 'ON' and 'AS' syntax.

Thanks so much Erland.

I appreciate both the answer and the education.
kind regards
Jun 21 '06 #6
Jeff Kish (ki*******@charter.net) writes:
Wow.
I'll have to study up some more on the 'ON' and 'AS' syntax.


The AS is optional and is only for defining an alias for the derived
table. The alias is always mandatory, though.

The ON is part of the newer ANSI syntax for joins which was introduced to
handle left joins, but once you have get used to it, you will use it for
all you joins, because it makes the queries so much clearer.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 21 '06 #7
On Wed, 21 Jun 2006 06:56:24 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:
Jeff Kish (ki*******@charter.net) writes:
Wow.
I'll have to study up some more on the 'ON' and 'AS' syntax.


The AS is optional and is only for defining an alias for the derived
table. The alias is always mandatory, though.

The ON is part of the newer ANSI syntax for joins which was introduced to
handle left joins, but once you have get used to it, you will use it for
all you joins, because it makes the queries so much clearer.

study study study.. it never ends...
Thanks again. Bonus that it works for sql server 2000 as well, I imagine, the
newer version.
Regards
Jeff Kish
Jun 22 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Tom Loach | last post: by
1 post views Thread by David Horowitz | last post: by
7 posts views Thread by Sameh Ahmed | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.