title: Sql Server - Query help (group by with having MAX)
body: <p>HI,</p>
  
  <p>I'm having a huge problem with one query, this is the data that I have in my table:</p>
  
  <pre><code>entityId             groupId              groupDepth
  -------------------- -------------------- -----------
  NULL                 1090                 0
  56                   1090                 1
  222                  1090                 1
  226                  1090                 1
  227                  1090                 1
  228                  1090                 1
  234                  1090                 1
  248                  1090                 2
  249                  1090                 2
  250                  1090                 2
  251                  1090                 2
  252                  1090                 1
  256                  1090                 1
  261                  1090                 1
  288                  1090                 1
  294                  1090                 1
  300                  1090                 1
  4691                 1090                 1
  4694                 1090                 1
  4697                 1090                 1
  </code></pre>
  
  <p>So what I would like to do, is to obtain the row with the highest groupDepth when given the entityId and groupId. Example results:</p>
  
  <pre><code>input: entityId = 294, groupId = 1090
  entityId             groupId              groupDepth
  -------------------- -------------------- -----------
  294                  1090                 1
  
  
  input: entityId = 113, groupId = 1090
  entityId             groupId              groupDepth
  -------------------- -------------------- -----------
  NULL                 1090                 0
  </code></pre>
  
  <p>I was thinking about something like this:</p>
  
  <pre><code>SELECT * FROM [dbo].[EntityGroup] a 
  WHERE EXISTS
  (
      SELECT   groupId
      FROM     [dbo].[EntityGroup] b
      WHERE    
                (b.entityId is null or b.entityId = 294) AND
                b.groupId = a.groupId
      GROUP BY  b.groupId
      HAVING    a.groupDepth = max(b.groupDepth) and
                a.entityId = b.entityId 
  )
  </code></pre>
  
  <p>Any help will be greatly appreciated!</p>