Select statement with MAX() aggregation in the Where Clause

Question

I have a database table which stores membership renewals each year. When insert the renewal record, an 'expiryDate' column is written with a date (31/8/[nextyear]).

So, as an example, let's say a member with memberID = 99 renews in 2007, 2008 and 2009, he will have 3 records (one for each year), with an 'expiryDate' recorded in each. If I do a

SELECT MAX(YEAR(expiryDate)) as maxExpiry 
  FROM renewals 
 WHERE memberID = 99

...I will get 2010 back.

What I would like to do is return ALL records where the MAX(YEAR(expiryDate)) is a given year.. for example,

SELECT * 
  FROM renewals 
 WHERE MAX(YEAR(expiryDate)) = '2010';

This query won't work as an aggregation can't be used in a where clause outside a subquery, but I can't quite work out how to structure the subquery... or even if this could be done a better way than using a subquery.

RET · Accepted Answer · 2011-11-11T03:34:12.713

5

A predicate based on an aggregated column uses a HAVING clause, rather than a WHERE.

If you only need the memberID, this is straightforward enough:

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

You can also do this as a sub-query if you need to get other columns from that table, ie:

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

UPDATE

It is correct as @OMG Ponies pointed out that this is not enough if you need to select additional columns from that one row in renewals. If that is required, you could use:

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

edited Nov 11 '11 at 03:34

answered Nov 11 '11 at 03:08

RET

9,100
1
28
33

@Jason, Additionally to THIS answer, add a WHERE clause of the date equal or greater than Jan 1, 2010... this will eliminate all old renewals prior that you would be throwing out anyhow... Don't know how large your table is, for performance issues. – DRapp Nov 11 '11 at 03:12
Worked perfectly.. thanks for the scope of variations and options!! It is actaully part of a larger dynamic query, so have gone for the subquery using HAVING.. worked a treat.. Also, thanks for the tip DRapp, great idea! Thanks all! Jason – Jason Nov 11 '11 at 11:20

score 1 · Answer 2 · answered Nov 11 '11 at 03:07

1

Use GROUP BY

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

answered Nov 11 '11 at 03:07

Yada

30,349
24
103
144

score 1 · Answer 3 · answered Nov 11 '11 at 03:16

For SQL Server 2005+, use:

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

The CTE isn't the real reason for being 2005+ -- it's the use of ROW_NUMBER because it can be re-written to not use the CTE.

The issue with the subquery is that getting a memberid (like you see in other answers) isn't enough to join to a copy of the RENEWALS table. You'll get all the records for those members, and still need to filter out what you are looking for.

Indeed, that is true. In my answer I have assumed that since memberID is duplicate in the `renewals` table, it must be a FK to somewhere else, which I have assumed to be `members`. Other arrangements are required to join back to `renewals`. — RET, Nov 11 '11 at 03:30

score 1 · Answer 4 · edited Jul 29 '20 at 18:30

This question is a couple of months old now and has an accepted answer as well as two more valid answers. Still, I am adding another one:

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
    );

Why? All previous answers will be slow for big tables because they cannot use an index on expiryDate. This one can. Aaron Bertrand (also active on SO) wrote a blog about the topic here - which agrees in striking detail with what I keep preaching for PostgreSQL.

Being able to use an index is way more important than other details of the query style here, as far as performance is concerned.

Also, this query prevents multiple rows for the same member. It only returns the latest row for 2010 per member - if there should be multiple entries for the year. Should not occur according to the description, but there can easily be exceptions. I assume that is what is needed. @OMG Ponies' answer is the only answer so far that observes this detail. Ironically it was also the only one without an upvote, until just now.

Select statement with MAX() aggregation in the Where Clause

4 Answers4