CODEWITHSUNDEEP
sqlsql-server
Jin K
Jin K

Reputation: 1

Getting MAX Result in a INNER JOIN?

I've got two tables: TraceJobs and TraceRoutes. The table structure looks something like this:

TraceJobs:

            id    title
             --    -----
              1     trace 1
              2     trace 2

TraceRoutes:

            id    tracejobs_id    hopnumber      url            RTT
             --    -------          ----------   --------           ---
              1       1               1          xyz123.contoso.com     1
              2       1               2          xyz124-2.contoso.com   5
              3       1               3          abc124-1.contoso.com   7
              4       1               4         abc124-1.contoso.com    10
              5       1               5          abc124-1.google.com    15
              6       1               6          abc1267-1.google.com   20
              7       2               1          xyz123.contoso.com     1
              8       2               2          xyz124-2.contoso.com   5
              9       2               3          abc124-1.contoso.com   7
              10      2               4         abc124-1.contoso.com    15
              11      2               5          abc124-1.google.com    17
              12      2               6          abc1267-1.google.com   20

TraceJobs table just keeps track of all the traceroutes I completed. TraceRoutes table keeps track of the traceroute from each of the tracejobs I completed. And there's a one to many relationship.

My goal is to join these tables so I can get the highest value of the RTT for URL matching ".contoso.com". Here's how I want to output to look like... 

            tracejobs_id    title           url                  RTT
            ----------    -------------    --------------    -------------
                     1    trace 1            contoso.com       10
                     2    trace 2            contoso.com       15

Any idea how I can accomplish this with SQL statement?

Thanks!

Upvotes: 0

Views: 57

Answers (3)

Claus Nielsen
Claus Nielsen

Reputation: 551

Something like this should work

SELECT tj.id AS 'tracejobs_id', 
    tj.title, 
    'contoso.com' AS url, 
    MAX(tr.RTT) AS 'RTT'
FROM TraceJobs tj JOIN TraceRoutes tr 
    ON tj.id = tr.tracejobs_id
WHERE tr.url LIKE '%contoso.com%'
GROUP BY tj.id, tj.title;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could use max a group by 

select r.tracejobs_id, j.title,  r.url,   max(r.RTT)
from TraceJobs j
inner join  TraceRoutes r on j.id = r.tracejobs_id
where r.url like ('%.contoso.com')
group by r.tracejobs_id, j.title,  r.url

Upvotes: 0

JNevill
JNevill

Reputation: 50034

Could you just aggregate with a GROUP BY?

SELECT t2.tracejobs_id, t1.title, 'contoso.com' as url, max(t2.rtt)
FROM tracejobs t1
    INNER JOIN traceRoutes t2 ON
         t1.id = t2.tracejobs_id
WHERE url like '%contoso.com'
GROUP BY tracejobs_id, title

Upvotes: 5

Related Questions