CODEWITHSUNDEEP
sqlt-sqljoinaggregate-functions
dursk
dursk

Reputation: 4445

Joins and Aggregate Functions

I have 3 tables I need to join together. Once I join the first two, I'm going to have two columns, let's call them A and B.

The relationship between A and B is many-to-many. So we can have:

A    B
1    1 
1    2
2    1
2    3

Then, I need to join with a third table on the B column, giving me:

A    B    C
1    1    5 
1    2    6
2    1    9
2    3    2

Now for my final result I only want one row for each unique A value, and I want to select that row based upon the MAX C value across that given A.

So in this example the final value would be:

A    B    C
1    2    6
2    1    9

I have the following query which works as expected, but I am fairly certain it is not the best way of doing it:

SELECT 
    Temp.A,
    Temp.B,
    Temp.C1
FROM
    (SELECT DISTINCT 
        T1.A, 
        T2.B,
        MAX(T3.C) OVER(PARTITION BY T1.A) AS C1
    FROM T1 
        INNER JOIN T2 ON T1.X = T2.X
        INNER JOIN T3 ON T2.B = T3.B) Temp
    INNER JOIN T3 ON T3.B = Temp.B
WHERE Temp.C1 = T3.C

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You query can be simplified:

  1. You don't need select distinct in the subquery.
  2. You don't need to join back to T3.
  3. You can select the C value in the subquery.

Here is the revision:

SELECT Temp.A, Temp.B, Temp.C
FROM (SELECT T1.A, T2.B, T3.C, MAX(T3.C) OVER (PARTITION BY T1.A) AS C1
      FROM T1 INNER JOIN
           T2
           ON T1.X = T2.X INNER JOIN
           T3
           ON T2.B = T3.B
     ) Temp
WHERE Temp.C1 = Temp.C

Do note that if T3 has duplicate maximum values, then this will return duplicates. To get just one, you can use row_number() instead:

SELECT Temp.A, Temp.B, Temp.C
FROM (SELECT T1.A, T2.B, T3.C,
             ROW_NUMBER() OVER (PARTITION BY T1.A ORDER BY T3.C DESC) AS seqnum
      FROM T1 INNER JOIN
           T2
           ON T1.X = T2.X INNER JOIN
           T3
           ON T2.B = T3.B
     ) Temp
WHERE seqnum = 1;

Upvotes: 1

Related Questions