title: List MAX and MIN in the same column in SQL Server
body: <p>I'm attempting to list the county with the highest poverty rate and that with the lowest poverty rate under the same column next to the State each belongs to. Like this:</p>
  
  <pre><code>+----------+--------+--------------+
  | State    | County | Poverty Rate |
  +----------+--------+--------------+
  | Delaware | AB     | 0.1234       |
  +----------+--------+--------------+
  | Delaware | CD     | 0.6789       |
  +----------+--------+--------------+
  | Virginia | EF     | 0.1357       |
  +----------+--------+--------------+
  | Virginia | GH     | 0.4680       |
  +----------+--------+--------------+
  </code></pre>
  
  <p>But I only got as far as showing three columns: State, Highest Poverty Rate, Lowest Poverty Rate, with the code below:</p>
  
  <pre><code>;WITH poverty_CTE AS (
      SELECT
          state
          ,County
          ,AVG(pctPoor)    AS 'Poverty'
      FROM [SQLBook].[dbo].[ZipCensus]
      WHERE County IS NOT NULL
      GROUP BY County, state
  )
  SELECT
      CASE state
          WHEN '10' THEN 'Delaware'
          WHEN '51' THEN 'Virginia'
          ELSE 'Others'
      END                     AS State
      ,MAX(Poverty)           AS 'Highest Poverty Rate'
      ,MIN(Poverty)           AS 'Lowest Poverty Rate'
  FROM poverty_CTE
  WHERE state IN (10, 51)
  GROUP BY State
  ORDER BY State
  </code></pre>
  
  <p>This is the result:</p>
  
  <pre><code>+------------+----------------------+---------------------+
  | State      | Highest Poverty Rate | Lowest Poverty Rate |
  +------------+----------------------+---------------------+
  | Delaware   | 0.6789               | 0.1234              |
  +------------+----------------------+---------------------+
  | Washington | 0.4680               | 0.1357              |
  +------------+----------------------+---------------------+
  </code></pre>
  
  <p>Is it possible to achieve what I want without complicated subqueries?</p>
  
  <p>Thank you for your help!</p>