Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PersonID with Max(Version) from it... Expand / Collapse
Author
Message
Posted Thursday, August 9, 2012 9:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 5:14 AM
Points: 53, Visits: 180
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
:)
Post #1342746
Posted Thursday, August 9, 2012 9:16 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1342753
Posted Thursday, August 9, 2012 9:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1342792
Posted Thursday, August 9, 2012 10:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1342839
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse