Posted by Roy Harvey on 07/26/07 16:23

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@gmail.com> 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

[Back to original message]