I need to get the latest 'version' from multiple records. For example, in the records below I need to get version 9 from AMP, version 7 for ANZ, version 3 for BHP and so on. I have this working with a CTE but I'm sure theres a better way.
Some test data:
CREATE TABLE #tmp (Index nvarchar(8), IndexName nvarchar(128), Symbol nvarchar(8), Version int, Name nvarchar(256))
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',1,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',2,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',3,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',4,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',5,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',6,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',7,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',8,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','AMP',9,'AMP Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','ANZ',1,'Australia and New Zealand Banking Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','ANZ',2,'Australia and New Zealand Banking Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','ANZ',3,'Australia and New Zealand Banking Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','ANZ',4,'Australia and New Zealand Banking Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','ANZ',5,'Australia and New Zealand Banking Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','ANZ',6,'Australia and New Zealand Banking Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','ANZ',7,'Australia and New Zealand Banking Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','BHP',1,'BHP Billiton Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','BHP',2,'BHP Billiton Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','BHP',3,'BHP Billiton Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','BXB',1,'Brambles Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','CBA',1,'Commonwealth Bank of Australia')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','CBA',2,'Commonwealth Bank of Australia')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','CSL',1,'CSL Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','CSL',2,'CSL Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','CSL',3,'CSL Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','IAG',1,'Insurance Australia Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','MQG',1,'Macquarie Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','MQG',2,'Macquarie Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','MQG',3,'Macquarie Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','MQG',4,'Macquarie Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','NAB',1,'National Australia Bank Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','NAB',2,'National Australia Bank Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','NAB',3,'National Australia Bank Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','NAB',4,'National Australia Bank Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','NAB',5,'National Australia Bank Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','QBE',1,'QBE Insurance Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','QBE',2,'QBE Insurance Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','RIO',1,'Rio Tinto Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','RIO',2,'Rio Tinto Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','SUN',1,'Suncorp Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','SUN',2,'Suncorp Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','SUN',3,'Suncorp Group Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','WBC',1,'Westpac Banking Corporation')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','WBC',2,'Westpac Banking Corporation')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','WBC',3,'Westpac Banking Corporation')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','WBC',4,'Westpac Banking Corporation')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','WBC',5,'Westpac Banking Corporation')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','WES',1,'Wesfarmers Limited')
INSERT INTO #tmp VALUES ('XTL','S&P/ASX 20','WES',2,'Wesfarmers Limited')