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\$
0

2 Answers 2

Reset to default
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\$
2
  • \$\begingroup\$ you need to add aliases and a clause WHERE t1.id = t2.id to your sub query probably \$\endgroup\$ Commented Aug 28, 2015 at 20:45
  • \$\begingroup\$ @JosephNields, ah, I didn't see the grouping in your question. I'll update my answer \$\endgroup\$ Commented Aug 28, 2015 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\$
2
  • \$\begingroup\$ Is there a particular reason for using a ranking function? Does this result in a performance increase? Why? \$\endgroup\$ Commented 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\$ Commented Aug 28, 2015 at 21:02

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.