MS SQL return Min count homeMDB

Hello All,

I have a table with a column called homeMDB which contains data
attribute value of users homeMDB from Active directory.
There are about 20 distinct mail stores all on 1 server used by 2000
users. I would like to return which mail store has the lease number of
user accounts.

I thought something like the following would work but it doesn't....
the Having clause is there to make sure that only records from the
main exchange server are returned. there is another one CN=MYSEREX305
with 1 or 2 accounts that i'm not interested in.

SELECT Min(tblADusers.homeMDB) AS MinOfhomeMDB
FROM tblADusers
HAVING (((Left([homeMDB],13))='CN=MYSEREX306'))

I get the Error "Column 'tblADusers.homeMDB' is invalid in the HAVING
clause because it is not contained in an aggregate function and there
is no GROUP BY clause.

Ideally i would like to return just 1 row (or 2 if both have equal
number of accounts) which has the least number of user mailboxes.


Many thanks for any help or advise :-)

Yas

Re: MS SQL return Min count homeMDB

Try this.

SELECT TOP 1 WITH TIES
homeMDB, count(*) as UserAccounts
FROM tblADusers
WHERE Left(homeMDB,13) = 'CN=MYSEREX306'
GROUP BY homeMDB
ORDER BY count(*)

Roy Harvey
Beacon Falls, CT

On Thu, 26 Jul 2007 06:26:52 -0700, Yas wrote:

>Hello All,
>
>I have a table with a column called homeMDB which contains data
>attribute value of users homeMDB from Active directory.
>There are about 20 distinct mail stores all on 1 server used by 2000
>users. I would like to return which mail store has the lease number of
>user accounts.
>
>I thought something like the following would work but it doesn't....
>the Having clause is there to make sure that only records from the
>main exchange server are returned. there is another one CN=MYSEREX305
>with 1 or 2 accounts that i'm not interested in.
>
>SELECT Min(tblADusers.homeMDB) AS MinOfhomeMDB
>FROM tblADusers
>HAVING (((Left([homeMDB],13))='CN=MYSEREX306'))
>
>I get the Error "Column 'tblADusers.homeMDB' is invalid in the HAVING
>clause because it is not contained in an aggregate function and there
>is no GROUP BY clause.
>
>Ideally i would like to return just 1 row (or 2 if both have equal
>number of accounts) which has the least number of user mailboxes.
>
>
>Many thanks for any help or advise :-)
>
>Yas

Re: MS SQL return Min count homeMDB

On 26 Jul, 18:23, Roy Harvey wrote:
> Try this.
>
> SELECT TOP 1 WITH TIES
> homeMDB, count(*) as UserAccounts
> FROM tblADusers
> WHERE Left(homeMDB,13) = 'CN=MYSEREX306'
> GROUP BY homeMDB
> ORDER BY count(*)
>
> Roy Harvey
> Beacon Falls, CT
>

Thank You! that worked really well.