Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please check Field4 result.

    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    Hi,

    Thanks for the quick reply.

    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. #4
    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. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Please check Field4 result.
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by g11DB
    Not even a need for self-join and aggregate function.
    |Actually, it has both!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    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. #9
    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. #10
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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