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

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 <yasar1 (AT) gmail (DOT) com> wrote:

On 26 Jul, 18:23, Roy Harvey <roy_har... (AT) snet (DOT) net> wrote:
Thank You! that worked really well.