# How to select rows which have max and min of count?

I have the following table in SQL Server 2012:

``````CREATE TABLE [MyTable] (
[Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
[MyGroup] INT NOT NULL
);
``````

I want to output a table of the form

``````    | MyGroup | Count
Max |         |
Min |         |
``````

For example, if I have `30` rows in MyTable where MyGroup = `1`, `20` rows where MyGroup = `2`, and `10` rows where MyGroup = `3`, then

``````    | MyGroup | Count
Max | 1       | 30
Min | 3       | 10
``````

What sort of query would output this information?

This is an alternative formulation of Travis's answer which avoids the need to sort the `COUNT` in both directions.

``````WITH s
AS (SELECT MyGroup,
Count(MyGroup)              AS [Count],
MAX(Count(MyGroup)) OVER () AS [MaxMyGroup],
MIN(Count(MyGroup)) OVER () AS [MinMyGroup]
FROM   MyTable
GROUP  BY MyGroup)
SELECT Agg,
MyGroup,
V.[Count]
FROM   s
CROSS APPLY (VALUES ( 'Max', CASE WHEN [Count] = [MaxMyGroup] THEN [Count] END),
('Min', CASE WHEN [Count] = [MinMyGroup] THEN [Count] END))
V(Agg, [Count])
WHERE V.[Count] IS NOT NULL
``````
• Good one on using cross apply for 'agg'. I was trying to find other ways to populate the 'agg'. Didn't like the additional sorts either. Jun 29, 2013 at 20:02
``````WITH s AS (
SELECT MyGroup, Count(MyGroup) AS [Count],
RANK() OVER (ORDER BY Count(MyGroup)) AS [rasc],
RANK() OVER (ORDER BY Count(MyGroup) DESC) AS [rdesc]
FROM MyTable
GROUP BY (MyGroup)
)
SELECT
CASE
WHEN [rasc] = 1 THEN 'Min'
ELSE 'Max'
END AS 'Agg',
[MyGroup],
[Count]
FROM s
WHERE [rasc] = 1 OR [rdesc] = 1
``````

SQLFiddle

Update: Martin's alternative is much efficient.

Here is another similar approach to Martin's solution,

``````WITH s AS (
SELECT MyGroup, Count(MyGroup) AS [Count],
MAX(Count(MyGroup)) OVER () AS [Max],
MIN(Count(MyGroup)) OVER () AS [Min]
FROM MyTable
GROUP BY MyGroup
)
SELECT
CASE
WHEN [Count] = [Max] THEN 'Max'
ELSE 'Min'
END AS [Agg],
[MyGroup],
[Count]
FROM s
WHERE [Count] IN ([Max], [Min]);
``````
• The `IN` version is simpler than the `UNPIVOT` with `APPLY`. One difference would be if there was only one group in `MyTable` this would return one row rather than two. Jun 29, 2013 at 21:11
• Thanks @MartinSmith for pointing that out. Anyone interested on how UNPIVOT with APPLY work could find more detail from this link bradsruminations.blogspot.co.uk/2010/02/…. You mentioned that in one of your old post as well. Jun 30, 2013 at 2:42