2
\$\begingroup\$

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 <= 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?

\$\endgroup\$
3
\$\begingroup\$

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)
\$\endgroup\$
  • \$\begingroup\$ you need to add aliases and a clause WHERE t1.id = t2.id to your sub query probably \$\endgroup\$ – Joseph Nields Aug 28 '15 at 20:45
  • \$\begingroup\$ @JosephNields, ah, I didn't see the grouping in your question. I'll update my answer \$\endgroup\$ – user2023861 Aug 28 '15 at 20:51
1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ Is there a particular reason for using a ranking function? Does this result in a performance increase? Why? \$\endgroup\$ – Joseph Nields Aug 28 '15 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\$ – Joseph Nields Aug 28 '15 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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