Ok, here's what I came up with.
It is imporant to understand the quirky update concept as well as the requirements that need to be fulfilled. So, I urge you to carefully read the article Chris mentioned (the same article is referenced in my sample code, too...)
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 + VolumeELSE 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