Re: SQL CORR SUBQUERY EXPERTS

A little left of plumb wrote:
>
> In article <4tasrc$r8k_at_newsbf02.news.aol.com>, mgreen1962_at_aol.com (MGreen1962)
> writes:
>
> > Hello, 2 all that would try to answer this question.
> >
> > How would I write a query that returns group rows where the count >= some
> > value.
> >
> > i.e.
> > select table_name,count(*)
> > from sys.dba_constraints
> > where count(*) >= 7
> > group by table_name;
> >
> > I know the count(*) function cannot be used in a where clause. I tried to
> > use a correlated subquery and my system locked each time. My query is as
> > follows:
> >
> > select table_name,count(*) from sys.dba_constraints x
> > where to_number('1')>=(select count(*) from sys.dba_constraints where
> > x.table_name=table_name)
> > group by table_name;
>
> i think you want something like this:
>
> select table_name, constraints
> from (select table_name, count(*) constraints
> from sys.dba_constraints
> group by table_name)
> where constraints >= 7;
>
> be careful though, since this query may not be returning exactly what you
> want in the way of info. for instance, more than one user may own a table
> with the same name. this query will merge all constraints for all owners of
> a particular table into its returned rows...
>
> HTH david

Try this:

select table_name, count(*)
  from sys.dba_constraints
  group by table_name
  having count(*) >= 7;

This should give you counts for those tables with more than 7 entries.... Received on Sat Jul 27 1996 - 00:00:00 CEST