Find Minimum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find Minimum

Author

Topic

ejbatu
Starting Member

21 Posts

Posted - 2010-09-23 : 10:08:03

Hi,

Based on the data below, how would I find the minimum frees pace of only one drive for each client and subclients?

For example, I would like the result to be:


ClientID     SubClientID    DriveLetter    FreeSpace
100            100                C               41411
103            101                C               30334
104            102                C               28720
105            103                C               14387
105            104                C               4534
107            108                C               15268
107            109                C               15268
114            119                D               103839


CREATE TABLE #SerDriveInfo
        (ClientID       INT
        ,SubClientID    INT
        ,ServerName     VARCHAR(20)
        ,DriveLetter    VARCHAR(2)
        ,FreeSpace      INT)

INSERT INTO #SerDriveInfo
SELECT 100,     100,    'ACDIGP',       'C',    41411 UNION
SELECT 103,     101,    'PTADTD',       'C',    30334 UNION
SELECT 103,     101,    'PTADTD',       'D',    100710 UNION
SELECT 104,     102,    'AVDTPAT',      'C',    28720 UNION
SELECT 104,     102,    'AVDTPAT',      'E',    116980 UNION
SELECT 105,     103,    'DCTATH02',     'C',    14387 UNION
SELECT 105,     103,    'DCTATH02',     'D',    30057 UNION
SELECT 105,     104,    'CLCRHA05',     'C',    4534 UNION
SELECT 105,     104,    'CLCRHA05',     'D',    70743 UNION
SELECT 106,     105,    'PTCDTC06',     'C',    16627 UNION
SELECT 106,     105,    'PTCDTC06',     'E',    143522 UNION
SELECT 107,     106,    'GRMCAR05',     'C',    14755 UNION
SELECT 107,     106,    'GRMCAR05',     'D',    205839 UNION
SELECT 107,     107,    'GRMCAR05',     'C',    14755 UNION
SELECT 107,     107,    'GRMCAR05',     'D',    205839 UNION
SELECT 107,     108,    'GRCAETDT01',   'C',    15268 UNION
SELECT 107,     108,    'GRCAETDT01',   'D',    97468 UNION
SELECT 107,     109,    'GRCAETDT01',   'C',    15268 UNION
SELECT 107,     109,    'GRCAETDT01',   'D',    97468 UNION
SELECT 107,     110,    'GRCAETDT01',   'C',    15268 UNION
SELECT 107,     110,    'GRCAETDT01',   'D',    97468 UNION
SELECT 107,     111,    'GRCAETDT01',   'C',    15268 UNION
SELECT 107,     111,    'GRCAETDT01',   'D',    97468 UNION
SELECT 108,     112,    'PTADTD',       'C',    30334 UNION
SELECT 108,     112,    'PTADTD',       'D',    100710 UNION
SELECT 109,     113,    'PTCNMATH02',   'C',    13151 UNION
SELECT 109,     113,    'PTCNMATH02',   'D',    20599 UNION
SELECT 110,     114,    'PTCDTC06',     'C',    16627 UNION
SELECT 110,     114,    'PTCDTC06',     'E',    143522 UNION
SELECT 111,     115,    'PTADTD',       'C',    30334 UNION
SELECT 111,     115,    'PTADTD',       'D',    100710 UNION
SELECT 112,     116,    'PTADTD',       'C',    30334 UNION
SELECT 112,     116,    'PTADTD',       'D',    100710 UNION
SELECT 112,     117,    'PTCDTC06',     'C',    16627 UNION
SELECT 112,     117,    'PTCDTC06',     'E',    143522 UNION
SELECT 113,     118,    'OKATVPG02',    'C',    18039 UNION
SELECT 113,     118,    'OKATVPG02',    'D',    65918 UNION
SELECT 114,     119,    'RMGDTPC02',    'C',    12125 UNION
SELECT 114,     119,    'RMGDTPC02',    'D',    103839 UNION
SELECT 114,     119,    'RMGDTPC02',    'N',    103839 UNION
SELECT 114,     119,    'RMGDTPC02',    'U',    103839 UNION
SELECT 114,     119,    'RMGDTPC02',    'X',    103839 UNION
SELECT 115,     120,    'RPDTSLO05',    'C',    972 UNION
SELECT 115,     120,    'RPDTSLO05',    'D',    125349 UNION
SELECT 115,     121,    'RPDTSLO05',    'C',    972 UNION
SELECT 115,     121,    'RPDTSLO05',    'D',    125349 UNION
SELECT 115,     122,    'RPDTSLO05',    'C',    972 UNION
SELECT 115,     122,    'RPDTSLO05',    'D',    125349 UNION
SELECT 115,     123,    'RPDTSLO05',    'C',    972 UNION
SELECT 115,     123,    'RPDTSLO05',    'D',    125349 UNION
SELECT 115,     124,    'RPDTSLO05',    'C',    972 UNION
SELECT 115,     124,    'RPDTSLO05',    'D',    125349 UNION
SELECT 115,     125,    'RPDTSLO05',    'C',    972 UNION
SELECT 115,     125,    'RPDTSLO05',    'D',    125349 UNION
SELECT 115,     126,    'RPDTSLO05',    'C',    972 UNION
SELECT 115,     126,    'RPDTSLO05',    'D',    125349

--SELECT * FROM #SerDriveInfo

/**
SELECT CLIENTID, SUBCLIENTID, SERVERNAME, DRIVELETTER, MIN(FREESPACE) AS FREESPACE
FROM #SerDriveInfo 
GROUP BY CLIENTID, SUBCLIENTID, SERVERNAME, DRIVELETTER
ORDER BY CLIENTID, SUBCLIENTID, DRIVELETTER 
**/

--DROP TABLE #SerDriveInfo

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-23 : 10:19:23

Is this you want ?


SELECT CLIENTID, SUBCLIENTID, SERVERNAME, DRIVELETTER, FREESPACE
FROM 
( 
SELECT CLIENTID, SUBCLIENTID, SERVERNAME, DRIVELETTER, FREESPACE,
ROW_NUMBER() OVER (PARTITION BY CLIENTID, SUBCLIENTID ORDER BY FREESPACE ) RowNo
FROM #SerDriveInfo 
) A
WHERE RowNo = 1