sql server (alternate): MS SQL return Min count homeMDB -- sql server (alternate)

MS SQL return Min count homeMDB

Yas
7/26/2007 6:26:52 AM

sql server (alternate):

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

Roy Harvey
7/26/2007 4:23:28 PM

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

[quoted text, click to view]

Re: MS SQL return Min count homeMDB

Yas
8/7/2007 3:05:56 AM

[quoted text, click to view]

Thank You! that worked really well.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.