SET NOCOUNT ON

--- original data
CREATE TABLE #dr_temp(
[PK] [INT] IDENTITY(1,1) NOT NULL,
[ID] [INT] NOT NULL,
Code [NVARCHAR](10) NOT NULL,
[VALUE] [INT] NOT NULL,
[Volume] [INT] NOT NULL
)

INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',40,30778)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',40,24860)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',40,82043)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',41,136116)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',42,106886)
INSERT INTO #dr_temp(ID,Code,VALUE,Volume) VALUES(1,'120',45,179646)

-- column added to hold RunningTotal values 
ALTER TABLE #dr_temp ADD RunningTotal INT

-- add clustered index to guarantee the specific order for the running total
CREATE CLUSTERED INDEX IX_#dr_temp_ID_Volume ON #dr_temp (ID,PK); 

-- perform the "quirky update"
-- for details, please see Jeff Modens related article: 
-- Link: http://www.sqlservercentral.com/articles/T-SQL/68467/

DECLARE @PrevID       INT
DECLARE @RunningTotal INT

UPDATE #dr_temp     
SET 
	@RunningTotal = RunningTotal = 
		CASE
			WHEN ID = @PrevID THEN @RunningTotal + Volume	ELSE Volume
		END,
	@PrevID = ID   
FROM #dr_temp  
WITH (TABLOCKX) OPTION (MAXDOP 1)

;WITH cte AS -- calculate the median per ID
(
	SELECT 
		pk,
		ID, 
		MedianDiff = (RunningTotal - (MAX(RunningTotal) OVER (PARTITION BY id))/2.0 )
	FROM #dr_temp
), cte2 AS -- find the PK values holding values larger than the median and number thos PK's
(
	SELECT  
		pk AS pkSub,
		ID, 
		ROW_NUMBER() OVER(PARTITION BY id ORDER BY pk ) AS ROW
	FROM   cte 
	WHERE MedianDiff > 0
)
-- final output: values of the row that holds the 2nd value larger than the median
SELECT  #dr_temp.*
FROM   cte2 
	INNER JOIN #dr_temp
	ON cte2.id = #dr_temp.id
		AND pksub=pk
WHERE ROW=2

DROP TABLE #dr_temp