# Thread: Self Join and Aggregates

1. Registered User
Join Date
Jul 2006
Posts
111

## Unanswered: Self Join and Aggregates

Hi all,

Someone once posted here an excellent self join code on aggregates but just couldn't find it. The problem is similar as follows:
Code:
PriKey    Field1    Field2        Field3    Field4
1          54        Ninna             42         1
2          2         Sybil               53         2
3          6         Michelle           44         3
4          12        Abigail            47         4
5          67        Geneana          55         5
6          23        Abigail            40         4
7          21        Evelyn            41         6
8          6         Sybil               57         2
9          85        Ninna              57         1
10         3         Sybil              49         2
11         78        Evelyn           50         6
12         4         Michelle          51         3
I want to group by on Field4 and for each, I want the maximum in Field1 such that the output is as follows:

Code:
PriKey    Field1    Field2         Field3    Field4
8           6         Sybil             57         2
3           6         Michelle         44         3
9           85        Ninna            57         1
6           23        Abigail           40         4
11         78        Evelyn           50         6
5           67        Geneana        55         5
I believe the solution involves a self join and the max aggregate function. Thanks in advance.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912

Can you confirm that, for each value of Field4, Field2 is ALWAYS known?
Can you confirm that every combination of Field4 and Field1 is unique?

What version of SQL Server?

3. Registered User
Join Date
Jul 2006
Posts
111
Hi,

For the 1st question, yes. Field4 and Field2 are 1-1 correspondence.

For the 2nd question, yes as well.

SQL Server 2005.
Last edited by g11DB; 04-07-09 at 08:51.

4. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
SELECT PriKey, Field1, Field2, Field3, Field4
FROM tbl t
WHERE Field1 =
(SELECT MAX(Field1)
FROM tbl
WHERE Field4 = t.Field4) ;

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by pootle flump
Actually I meant "Please check the result where field4 equals 4"

Dave's solution works for 2k and 2k5. There's a 2k5 solution too.

6. Registered User
Join Date
Jul 2006
Posts
111
Thank you, I think it worked. Not even a need for self-join and aggregate function.

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by g11DB
Not even a need for self-join and aggregate function.
|Actually, it has both!

8. Registered User
Join Date
Jul 2006
Posts
111
well yea i'm a bit sleepy already missed the MAX function, but where is the self join?

9. Registered User
Join Date
Dec 2007
Location
London, UK
Posts
741
A correlated subquery isn't really a join because it just returns a scalar value. You could write a similar query with a join and an aggregate but I think it's unlikely to be as efficient and probably harder to understand:

SELECT DISTINCT t1.PriKey, t1.Field1, t1.Field2, t1.Field3, t1.Field4
FROM tbl t1,tbl t2
WHERE t1.Field4 = t1.Field4
GROUP BY t1.PriKey, t1.Field1, t1.Field2, t1.Field3, t1.Field4, t2.Field4
HAVING t1.Field1 = MAX(t2.Field1);

10. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Whoops! I misread that as a derived table

Self join (you can use another variation with a standard join too, I just used cross apply):
Code:
SELECT PriKey, Field1, Field2, Field3, Field4
FROM tbl t
CROSS APPLY
(SELECT Field4
FROM tbl
WHERE MAX(Field1)= t.Field1) AS derT
At least I count it as a self join. Really strictly, I suppose it is not.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•