Min of a count
In SPL I need to take the minimum of a group. For
e.g.
select count(*),group_field
from table
where ....
group by 2
I need to take the minimum of count(*).
If I do
select min(count(*)),group_field
from table
where ....
group by 2
it gives a syntax error that aggrgrate of aggregrate is not allowed.
in dbaccess I can use
select first 1count(*),group_field
from table
where ....
group by 2
order by 1
But this is not allowed in SPL. How do it do it. At present I
am using foreach and exiting after fetching first row, which is minimum.
I am sure there must be a better approach.
order by 1 is not a problem.the problem is the INTO clause
which can't take more than 1 row. that's why I have to write
the sql which returns only 1 row.
----- Original Message -----
From: "Scott O'Rourke" <scott@eurolife.co.uk>
To: "Ravi Krishna" <rkrishna@farelogix.com>; <ids@iiug.org>
Sent: December 05, 2003 05:47
Subject: RE: Min of a count [2294]
>
> ORDER BY 1
>
> > -----Original Message-----
> > From: forum.subscriber@iiug.org
> > [mailto:forum.subscriber@iiug.org] On Behalf Of Ravi Krishna
> > Sent: 04 December 2003 21:09
> > To: ids@iiug.org
> > Subject: Min of a count [2294]
> >
> >
> > In SPL I need to take the minimum of a group. For e.g.
> > select count(*),group_field
> > from table
> > where ....
> > group by 2> >
> > I need to take the minimum of count(*).
> >
> > If I do
> >
> > select min(count(*)),group_field
> > from table
> > where ....
> > group by 2
> >
> > it gives a syntax error that aggrgrate of aggregrate is not allowed.
> >
> > in dbaccess I can use
> >
> > select first 1> > count(*),group_field
> > from table
> > where ....
> > group by 2
> > order by 1
> >
> > But this is not allowed in SPL. How do it do it. At present I
> > am using foreach and exiting after fetching first row, which
> > is minimum. I am sure there must be a better approach.
> >
> >
> >--------------------------------------------------------
> Eurolife Assurance Group plc, Eurolife House, 16 St John Street, London,
EC1M 4NT
> Tel: 020-7454 1151 Fax: 020-7454 1277
> www.eurolife.co.uk
> Registered in England, number 2471313
>
>
> This message (and any associated files) is intended only for the use of the
individual or entity to
which it is addressed and may contain information that is confidential,
subject to copyright or
constitutes a trade secret. If you are not the intended recipient you are
hereby notified that any
dissemination, copying or distribution of this message, or files associated
with this message, is
strictly prohibited. If you have received this message in error, please notify
us immediately by
replying to the message and deleting it from your computer. Messages sent to
and from us may be
monitored.
>
> Internet communications cannot be guaranteed to be secure or error-free as
information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain
viruses. Therefore, we
do not accept responsibility for any errors or omissions that are present in
this message, or any
attachment, that have arisen as a result of e-mail transmission. If
verification is required, please
request a hard-copy version. Any views or opinions presented are solely those
of the author and do
not necessarily represent those of the company.
>
>
>