GROUP BY

Question

Post reply

GROUP BY

Jason Bunn

SSCommitted

Points: 1713
More actions
August 7, 2007 at 10:34 am

#197230

Hello,
I hate to ask such a simple question, but here it goes.
Suppose I have a table such as:
SERVER_NAME     DATABASE_NAME       SIZE
A                       ABC                     35
A                       DEF                     50
B                       GHI                      20
B                       JKL                      15
and I want to find the largest database on each server.
If I do:
select server_name, max(size) from tableA group by server_name
it works, but I can't get the database name. If I add it to the select statement, I get an error since the database name is not part of the group_by or aggregate clause. If I group by server_name, database_name, I get all records, which I don't want either. For the above example, I'd only want two rows returned:
A    DEF   50
B    GHI    20

Any help would be appreciated.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike Howell Mr or Mrs. 500 Points: 521 More actions · Answer 1

You could write your query like this

SELECT db.Server_Name, db.Database_Name, db.Size

FROM DatabaseTable db

JOIN (

select server_name, max(size) as size from tableA group by server_name

) tbl

ON db.Server_Name = tbl.Server_Name

AND db.Size = tbl.Size

This should link the table with the records with the largest databases

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's how i would do it, there's probably a better way:

[edited after posting: Mike Howell beat me to it...same solution i think]

CREATE TABLE #TEST(

SERVER_NAME VARCHAR(30),

DATABASE_NAME VARCHAR(30),

DBSIZE INT)

INSERT INTO #TEST(SERVER_NAME, DATABASE_NAME, DBSIZE)

SELECT 'A','ABC',35 UNION

SELECT 'A','DEF',50 UNION

SELECT 'B','GHI',20 UNION

SELECT 'B','JKL',15

SELECT

#TEST.SERVER_NAME,

#TEST.DATABASE_NAME,

#TEST.DBSIZE

FROM #TEST

INNER JOIN (

SELECT

SERVER_NAME,

MAX(DBSIZE) AS DBSIZE

FROM #TEST

GROUP BY SERVER_NAME) X ON #TEST.SERVER_NAME = X.SERVER_NAME

AND #TEST.DBSIZE = X.DBSIZE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Colin Bickford Say Hey Kid Points: 693 More actions · Answer 3

Using Lowell's temporary table (thankyou Lowell):

SELECT SERVER_NAME, substring(max(right('00' + cast(DBSIZE as nvarchar(4)), 4) + DATABASE_NAME), 5, 3)

FROM #TEST

GROUP BY SERVER_NAME

will work very efficiently.

You can't directly find an aggregate of one column based upon another, but the above technique first concatenates them to get around this. The resultant expression can then be MAXd and then SUBSTRINGd to arrive at the required data.

PS. I have assumed that DBSIZE will not be any larger than 9999. If it is, you'll have to use more 0s and increase the parameters of the functions RIGHT and SUBSTRING.

Colin Bickford Say Hey Kid Points: 693 More actions · Answer 4

Sorry, you wanted size aswell:

SELECT SERVER_NAME,

substring(max(right('00' + cast(DBSIZE as nvarchar(4)), 4) + DATABASE_NAME), 5, 3) DATABASE_NAME,

cast(max(right('00' + cast(DBSIZE as nvarchar(4)), 4)) as int)

FROM #TEST

GROUP BY SERVER_NAME