title: TSQL Select Min & Max row when grouping
body: <p>Lets say I have a table containing many many rows like this:</p>
  
  <pre><code>ID        Range         Range_begining        Profit
  ----------------------------------------------------
   1    (100-150)                    100           -20
   2    (200-250)                    200          40.2
   3    (100-150)                    100           100
   4    (450-500)                    450           -90
   ...
  </code></pre>
  
  <p>I'm doing a simple query like this:</p>
  
  <pre><code>SELECT max([Range]) AS 'Range'
       , count(ID) AS 'Count'
       , round(avg([Profit]), 2) AS 'AVG Profit'
      FROM
          Orders
      GROUP BY
          Range_begining
  </code></pre>
  
  <p>After this query is run I get results like this:</p>
  
  <pre><code>Range        Count        AVG Profit
  ------------------------------------
  (100-150)        2                40
  (200-250)        1              40.2
  (450-500)        1               -90
   ...
  </code></pre>
  
  <p>Quite simple :)</p>
  
  <p>What I need to do now is to select row with minimum and maximum profit where count is bigger than 10 (this is a parameter)</p>
  
  <p>I was able to get minimum value with this:</p>
  
  <pre><code>SELECT TOP 1 [Range], [AVG Profit] FROM (
       SELECT max([Range]) AS 'Range'
           , count(ID) AS 'Count'
           , round(avg([Profit]), 2) AS 'AVG Profit'
          FROM
              Orders
          GROUP BY
              Range_begining) X
  WHERE
      [Count]&gt;10
  ORDER BY 
      [AVG Profit] ASC --or DESC if I want max profit
  </code></pre>
  
  <p>I was thinking of doing an <code>UNION</code> for above query with ORDER BY DESC, but it isn't the best solution.</p>
  
  <p>What I need to do:<br />
  <strong>Select 2 rows: one with minimum, second with maximum AVG Profit when grouping by Range.</strong></p>
  
  <p><strong>EDIT:</strong>
  If I add 2 move columns to my main data table like this:</p>
  
  <pre><code>ID        Range         Range_begining        Profit        OrderDate     Company
  ---------------------------------------------------------------------------------
   1    (100-150)                    100           -20        2012-01-02          1
   2    (200-250)                    200          40.2        2012-03-22          0
   3    (100-150)                    100           100        2012-02-05          0
   4    (450-500)                    450           -90        2012-05-12          1
   ...
  </code></pre>
  
  <p>And then try to add 2 more conditions like this:</p>
  
  <pre><code>; with ordering as (
    SELECT max([Range]) AS 'Range'
       , count(ID) AS 'Count'
       , round(avg([Profit]), 2) AS 'AVG Profit'
       , row_number() over (order by avg([Profit])) rn_min
       , row_number() over (order by avg([Profit]) desc) rn_max
      FROM
          Orders
      GROUP BY
          Range_begining
      HAVING COUNT(ID) &gt; 10
      AND [Company]=@company
      AND (@from= '' OR [OrderDate]&gt;=@from)
      AND (@to= '' OR [OrderDate]&lt;=@to)
  )
  select [range], [count], [avg profit]
    from ordering
   where (rn_max = 1 or rn_min = 1)
  </code></pre>
  
  <p>I get an error because [Company] and [OrderDate] </p>
  
  <blockquote>
    <p>is invalid in the HAVING clause because it is not contained in either
    an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>How can I fix this?</p>
  
  <p><strong>EDIT2</strong>
  Got it working!</p>
  
  <pre><code>; with ordering as (
    SELECT max([Range]) AS 'Range'
       , count(ID) AS 'Count'
       , round(avg([Profit]), 2) AS 'AVG Profit'
       , row_number() over (order by avg([Profit])) rn_min
       , row_number() over (order by avg([Profit]) desc) rn_max
      FROM
          Orders
      WHERE
      [Company]=@company
      AND (@from= '' OR [OrderDate]&gt;=@from)
      AND (@to= '' OR [OrderDate]&lt;=@to)
      GROUP BY
          Range_begining
      HAVING COUNT(ID) &gt; 10
  )
  select [range], [count], [avg profit]
    from ordering
   where (rn_max = 1 or rn_min = 1)
  </code></pre>
  
  <p><strong>EDIT 3</strong>
  Can I return another column with description like this:</p>
  
  <pre><code>Range        AVG Profit               Description
  -------------------------------------------------
  (200-250)          40.2           Max profit here
  (450-500)           -90     Min profit, well done
  </code></pre>
  
  <p><strong>EDIT 4</strong>
  Fast answer (based on @Nikola Markovinović answer):</p>
  
  <pre><code>; with ordering as (
    SELECT max([Range]) AS 'Range'
       , count(ID) AS 'Count'
       , round(avg([Profit]), 2) AS 'AVG Profit'
       , row_number() over (order by avg([Profit])) rn_min
       , row_number() over (order by avg([Profit]) desc) rn_max
      FROM
          Orders
      WHERE
      [Company]=@company
      AND (@from= '' OR [OrderDate]&gt;=@from)
      AND (@to= '' OR [OrderDate]&lt;=@to)
      GROUP BY
          Range_begining
      HAVING COUNT(ID) &gt; 10
  )
      SELECT
      CASE WHEN rn_max=1 THEN 'This is max' ELSE 'Min' END AS 'Description'
      ,[range]
      ,[count]
      ,[avg profit]
      FROM ordering
      WHERE (rn_max = 1 or rn_min = 1)
  </code></pre>