Self Join and Aggregate Functions
-
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:
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 3I want to group by on Field4 and for each, I want the maximum in Field1 such that the output is as follows:
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 5I believe the solution involves a self join and the max aggregate function. Thanks in advance.
---------------------------------------------------------- Lorem ipsum dolor sit amet.
-
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:
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 3I want to group by on Field4 and for each, I want the maximum in Field1 such that the output is as follows:
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 5I believe the solution involves a self join and the max aggregate function. Thanks in advance.
---------------------------------------------------------- Lorem ipsum dolor sit amet.
Hi, Assuming the table is called "kh" and you want it ORDER BY Field1 you could try this. The syntax works with T-SQL (Sybase). You may need to amend it for your DB if different. SELECT t1.PriKey,t1.Field1, t1.Field2,t1.Field3,t1.Field4 FROM kh t1 INNER JOIN (SELECT MAX(Field1) AS Field1,Field4 FROM kh GROUP BY Field4) t2 ON (t1.Field4 = t2.Field4) WHERE t1.Field1 = t2.Field1 ORDER BY t1.Field1 I hope it helps. Cheers, Kevin
-
Hi, Assuming the table is called "kh" and you want it ORDER BY Field1 you could try this. The syntax works with T-SQL (Sybase). You may need to amend it for your DB if different. SELECT t1.PriKey,t1.Field1, t1.Field2,t1.Field3,t1.Field4 FROM kh t1 INNER JOIN (SELECT MAX(Field1) AS Field1,Field4 FROM kh GROUP BY Field4) t2 ON (t1.Field4 = t2.Field4) WHERE t1.Field1 = t2.Field1 ORDER BY t1.Field1 I hope it helps. Cheers, Kevin