Find Minimum
-
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
(ClientIDINT
,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
-
WITH CTE AS (
SELECT ClientID,SubClientID,DriveLetter,FreeSpace,
ROW_NUMBER() OVER(PARTITION BY ClientID,SubClientID ORDER BY FreeSpace,DriveLetter) AS rn
FROM #SerDriveInfo)
SELECT ClientID,SubClientID,DriveLetter,FreeSpace
FROM CTE
WHERE rn=1;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Great Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply