CODEWITHSUNDEEP
sql-server
Elphrian
Elphrian

Reputation: 71

How to get the corresponding value of the Max column

What if I have this data? and I need to get the time of the latest time and the roundtrip of that latest time???

     Server| Site | RoundTrip |   Time   |
       A      AA      200       09:02:17
       A      AA      100       09:02:16
       A      AA      150       09:02:14
       B      AA      250       09:02:15
       B      AA      200       09:02:18
       B      AA      270       09:02:16
       C      AA      100       09:02:19
       C      AA      150       09:02:18
       C      AA      200       09:02:15
       D      AA      300       09:02:13
       D      AA      200       09:02:15
       D      AA      250       09:02:14
       A      BB      200       09:02:58
       A      BB      100       09:02:49
       A      BB      150       09:02:53
       B      BB      150       09:02:50
       B      BB      350       09:02:54
       B      BB      250       09:02:53
       C      BB      200       09:02:56
       C      BB      175       09:02:55
       C      BB      250       09:02:57
       D      BB      260       09:02:50
       D      BB      200       09:02:52
       D      BB      140       09:02:51

I need to get the latest time of all the server in different sites and their corresponding roundtrip. Because in my code I also get the max value of the roundtrip.

     Server| AA-Roundtrip | Time-AA  | BB-Roundtrip | Time-BB   |
       A         200        09:02:17       200        09:02:58
       B         200        09:02:18       350        09:02:54
       C         100        09:02:19       250        09:02:57
       D         200        09:02:15       200        09:02:52

Any one can help me? Please..

Upvotes: 0

Views: 79

Answers (4)

Alex Yu
Alex Yu

Reputation: 3537

Something like this?

WITH LEFT_PART AS (
SELECT 
        SERVER,
        AA_Roundtrip = MIN(Roundtrip)
    FROM 
        DATA
    WHERE
        Site = 'AA'
    GROUP BY Server
),RIGHT_PART AS (
SELECT 
        SERVER,
        BB_Roundtrip = MIN(Roundtrip)
    FROM 
        DATA
    WHERE
        Site = 'BB'
    GROUP BY Server
)
SELECT 
        LP.Server
        ,AA_Roundtrip
        ,Time_AA = (
            SELECT TOP(1) time FROM DATA D
                WHERE
                    D.Server = LP.Server
                    AND D.Roundtrip = LP.AA_Roundtrip
                ORDER BY
                    D.time DESC
        ) 
        ,BB_Roundtrip
        ,Time_BB = (
            SELECT TOP(1) time FROM DATA D
                WHERE
                    D.Server = LP.Server
                    AND D.Roundtrip = RP.BB_Roundtrip
                ORDER BY
                    D.time  DESC    
        )
    FROM 
        LEFT_PART LP 
        JOIN RIGHT_PART RP ON LP.Server = RP.Server

Upvotes: 0

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

I think you SELECT MAX(RoundTrip) GROUP BY (Server, Site). You can use ROW_NUMBER to choose max value, like this:

;WITH CTE AS (
   SELECT * FROM (
      SELECT
         *,
         ROW_NUMBER() OVER(PARTITION BY Server, Site ORDER BY RoundTrip DESC) AS RN
      FROM Your_Table
   ) AS A
   WHERE RN = 1 -- choose max value RoundTrip)

-- using CROSS APPLY
SELECT A.Site, A.[AA-RoundTrip], A.[AA-Time], B.[BB-RoundTrip], B.[BB-Time]
   FROM (
      SELECT Site, RoundTrip AS [AA-RoundTrip], Time AS [AA-Time]
      FROM CTE WHERE Server = AA) AS A
   CROSS APPLY (
      SELECT Site, RoundTrip AS [BB-RoundTrip], Time AS [BB-Time]
      FROM CTE WHERE Server = BB AND Site = A.Site) AS B

-- using INNER JOIN
   SELECT A.Site, A.[AA-RoundTrip], A.[AA-Time], B.[BB-RoundTrip], B.[BB-Time]
   FROM (
      SELECT Site, RoundTrip AS [AA-RoundTrip], Time AS [AA-Time]
      FROM CTE WHERE Server = AA) AS A
   INNER JOIN (
      SELECT Site, RoundTrip AS [BB-RoundTrip], Time AS [BB-Time]
      FROM CTE WHERE Server = BB) AS B
     ON A.Site = B.Site

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

The following query uses PARTITION to calculate the maximum round trip per site and server and then uses CASE for figuring out the correct values.

select 
  Server,
  MAX(CASE WHEN Site ='AA' and [Time]=MaxRoundTrip Then RoundTrip else NULL end)  as [AA-Roundtrip],
  MAX(CASE WHEN Site='AA'  then MaxRoundTrip  else NULL end) as [Time-AA],
  MAX(CASE WHEN Site ='BB' and [Time]=MaxRoundTrip Then RoundTrip else NULL end)  as [BB-Roundtrip],
  MAX(CASE WHEN Site='BB'  then MaxRoundTrip  else NULL end) as [Time-BB]
from
(
  select *, MAX([Time]) Over(Partition by Server, Site) as maxroundtrip from t
) t
group by Server

Working SQL Fiddle: http://sqlfiddle.com/#!3/007a0/5

Upvotes: 0

Joel R Michaliszen
Joel R Michaliszen

Reputation: 4222

I think something in that line will works fine:

SELECT  AA.SERVER, 
        AA.[AA-ROUNDTRIP],
        AA.[TIME-AA],
        BB.[BB-ROUNDTRIP],
        BB.[TIME-BB]        
FROM
(SELECT SERVER, 
        MAX(ROUNDTRIP)[AA-ROUNDTRIP],
        TIME[TIME-AA]
  FROM ANYTABLE
  WHERE SITE= 'AA'
  GROUP BY SERVER,TIME) AA

INNER JOIN 
    (SELECT SERVER , 
            MAX(ROUNDTRIP)[BB-ROUNDTRIP],
            TIME[TIME-BB]
    FROM ANYTABLE
    WHERE SITE= 'BB'
    GROUP BY SERVER,TIME) BB

ON AA.SERVER=BB.SERVER

Upvotes: 1

Related Questions