I have the following table definition:

CREATE TABLE ProductPointValues (
    id          INT,
    effective   DATE,
    minPoints   INT,
    maxPoints   INT,

    CONSTRAINT PK PRIMARY KEY (id, effective),

INSERT INTO ProductPointValues 
    (1, '1/1/2015', 100, 200),
    (1, '2/1/2015',  50, 250),
    (1, '3/1/2015',   0, 300),
    (1, '1/1/2016', 500, 900)  

And am running this query to select values for the most recent version of an id:

SELECT P.id, P.minPoints, P.maxPoints
FROM ProductPointValues P
        SELECT id, MAX(effective) effective
        FROM ProductPointValues
        WHERE effective <= GETDATE()
        GROUP BY id
    ) T
ON P.id = T.id AND P.effective = T.effective


id          minPoints    maxPoints      
----------- -----------  ----------- 
1           0            300

is there an equivalent, more elegant way to write this using aggregate functions?


2 Answers 2


You can use a sub-query:

declare @tbl table (
    id          INT,
    effective   DATE,
    points      INT)

    (1, '1/1/2015', 123),
    (1, '2/1/2015', 234),
    (1, '3/1/2015', 345),
    (2, '4/1/2015', 123),
    (2, '5/1/2015', 234),
    (2, '6/1/2015', 345) 

select * from @tbl t1
where effective = (select max(effective) from @tbl t2 where t1.id = t2.id)
  • \$\begingroup\$ you need to add aliases and a clause WHERE t1.id = t2.id to your sub query probably \$\endgroup\$ Aug 28, 2015 at 20:45
  • \$\begingroup\$ @JosephNields, ah, I didn't see the grouping in your question. I'll update my answer \$\endgroup\$ Aug 28, 2015 at 20:51

Logically, what you want to do is partition the rows by id, then within each partition, pick the row with the latest the effective date. For clarity, then, you should write the query to reflect your intention, using an OVER clause. (The OVER clause is the SQL feature that is designed to let you run aggregate queries on each partition, which is just what you want.)

SELECT id, minPoints, maxPoints
    FROM (
        SELECT *
             , ROW_NUMBER() OVER (PARTITION BY id ORDER BY effective DESC) AS most_recent
        FROM ProductPointValues
    ) AS RecentProductPointValues
    WHERE most_recent = 1;

SQL Fiddle

  • \$\begingroup\$ Is there a particular reason for using a ranking function? Does this result in a performance increase? Why? \$\endgroup\$ Aug 28, 2015 at 20:48
  • \$\begingroup\$ It actually looks like a ranking function with an order by is less performant because it has to sort completely even when selecting just the top 1. This would be much worse if the table was larger, too. Doing an aggregate on an indexed column means just an index scan \$\endgroup\$ Aug 28, 2015 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.