August 9, 2012 at 9:10 am
Hi,
how can I get effectively max(version) to identify any PersonID with multiple VersionID?
PersonID version
1 1
1 2
1 3
2 1
2 2
3 1
4 1
4 2
and so on ...
Result
1 3
2 2
3 1
4 2
Thank you
Nicole
🙂
August 9, 2012 at 9:16 am
info 58414 (8/9/2012)
Hi,how can I get effectively max(version) to identify any PersonID with multiple VersionID?
That's what GROUP BY and MAX are for 🙂
DECLARE @persons TABLE(
personid INT,
version INT)
INSERT @persons
VALUES(1,1),(1,2),(1,3),(2,1),(2,2),(3,1),(4,1),(4,2)
SELECT
personid,
maxversion = MAX(version)
FROM
@persons
GROUP BY personid
ORDER BY personid
August 9, 2012 at 9:58 am
Curious, which is the more scalable solution?
DECLARE @persons TABLE(
personid INT,
version INT);
INSERT @persons
VALUES(1,1),(1,2),(1,3),(2,1),(2,2),(3,1),(4,1),(4,2);
SELECT
personid,
maxversion = MAX(version)
FROM
@persons
GROUP BY personid
ORDER BY personid;
WITH BaseData AS (
SELECT
personid,
version,
ROW_NUMBER() OVER (PARTITION BY personid ORDER BY version DESC) rn
FROM
@persons
)
SELECT
personid,
version
FROM
BaseData
where
rn = 1;
I guess I'll have to test this at home tonight.
August 9, 2012 at 10:36 am
Looking at the execution plan, group by version looks simplier, 4 operators versus 6. The table scan and sort have the same cost associated in both.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply