SQL query to select most recent version I have the following table definition:

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

    CONSTRAINT PK PRIMARY KEY (id, effective),
    CONSTRAINT FK FOREIGN KEY (id) REFERENCES Products(id)
)
;

INSERT INTO ProductPointValues 
VALUES
    (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
INNER JOIN 
    (
        SELECT id, MAX(effective) effective
        FROM ProductPointValues
        WHERE effective &lt;= GETDATE()
        GROUP BY id
    ) T
ON P.id = T.id AND P.effective = T.effective


returns

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


is there an equivalent, more elegant way to write this using aggregate functions?
You can use a sub-query:

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

INSERT INTO @tbl
VALUES
    (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)