Оператор Select с агрегацией MAX() в предложении Where

Предикат, основанный на агрегированном столбце, использует предложение HAVING, а не WHERE.

Если вам нужен только идентификатор участника, это достаточно просто:

SELECT memberID
  FROM renewals
  GROUP BY memberID
    HAVING MAX(YEAR(expiryDate)) = 2010

Вы также можете сделать это как подзапрос, если вам нужно получить другие столбцы из этой таблицы, например:

SELECT * FROM members
  WHERE memberID IN ( <<previous query>> )

ОБНОВЛЕНИЕ

Это правильно, поскольку @OMG Ponies указал, что этого недостаточно, если вам нужно выбрать дополнительные столбцы из этой строки в renewals. Если это необходимо, вы можете использовать:

SELECT * FROM renewals
  WHERE memberID IN ( SELECT memberID FROM renewals
                      GROUP BY memberID HAVING MAX(YEAR(expiryDate)) = 2010 )
    AND YEAR(expiryDate) == 2010

Используйте ГРУППИРОВАТЬ ПО

SELECT memberID, MAX(YEAR(expiryDate))
  FROM renewals 
GROUP BY memberID
HAVING MAX(YEAR(expiryDate)) = 2010

Для SQL Server 2005+ используйте:

WITH cte AS (
  SELECT r.*,
         ROW_NUMBER() OVER (PARTITION BY r.memberid
                                ORDER BY r.expirydate DESC) AS rnk
    FROM RENEWALS r)
SELECT c.*
  FROM cte c
 WHERE c.rnk = 1
   AND YEAR(c.expirydate) = 2010

CTE не является реальной причиной для того, чтобы быть 2005+ - это использование ROW_NUMBER, потому что его можно переписать, чтобы не использовать CTE.

Проблема с подзапросом заключается в том, что получения memberid (как вы видите в других ответах) недостаточно для присоединения к копии таблицы RENEWALS. Вы получите все записи для этих участников, и вам все равно нужно отфильтровать то, что вы ищете.

Этому вопросу уже несколько месяцев, и на него есть принятый ответ, а также еще два действительных ответа. Тем не менее, я добавляю еще один:

SELECT *
FROM   renewals r
WHERE  expiryDate >= '20100101'  -- unambiguous input format with any locale!
AND    expiryDate <  '20110101'
AND    NOT EXISTS (
    SELECT *
    FROM   renewals r0
    WHERE  r0.memberID   = r.memberID
    AND    r0.expiryDate > r.expiryDate
    );

Почему? Все предыдущие ответы будут медленными для больших таблиц, потому что они не могут использовать индекс для expiryDate. Это можно. Аарон Бертран (также активно работающий над SO) написал блог на тему здесь, что в поразительных деталях согласуется с что я продолжаю проповедовать для PostgreSQL.

Возможность использовать индекс гораздо важнее, чем другие детали стиля запроса, с точки зрения производительности.

Кроме того, этот запрос предотвращает создание нескольких строк для одного и того же члена. Он возвращает только последнюю строку за 2010 год для каждого члена, если для года должно быть несколько записей. По описанию не должно происходить, но легко могут быть исключения. Я предполагаю, что это то, что нужно. Ответ @OMG Ponies - пока единственный ответ, в котором учтена эта деталь. По иронии судьбы, до сих пор он был единственным, кто не проголосовал за.