August 28, 2018 at 6:28 am
Hi,
CREATE TABLE #GetMax
(
ID INT,
CName VARCHAR(10),
Value1 INT,
Changeval INT,
Value2 INT
)
INSERT INTO #GetMax ( ID,CName,Value1,Changeval,Value2 )
SELECT 1, 'Text1',1,0,1 UNION
SELECT 1, 'Text1',1,1,1 UNION
SELECT 1, 'Text1',1,2,1 UNION
SELECT 1, 'Text1',1,3,1 UNION
SELECT 1, 'Text1',1,4,1 UNION
SELECT 2, 'Text2',1,5,0 UNION
SELECT 2, 'Text2',1,6,0 UNION
SELECT 2, 'Text2',1,7,0
SELECT * from #GetMax
Need to get one max value of Value1 in one row and update the others to 0 based on ID and Need to get one max value of Value2 in one row and update the others to 0 based on ID
results like this..
SELECT 1, 'Text1',1,0,1 UNION
SELECT 1, 'Text1',0,1,0 UNION
SELECT 1, 'Text1',0,2,0 UNION
SELECT 1, 'Text1',0,3,0 UNION
SELECT 1, 'Text1',0,4,0 UNION
SELECT 2, 'Text2',1,5,0 UNION
SELECT 2, 'Text2',0,6,0 UNION
SELECT 2, 'Text2',0,7,0
Thanks,
PSB
August 28, 2018 at 7:16 am
Have you tried anything? The best way to begin here is with a SELECT to find the values you want. Given your results, I'm guessing you want to choose the row with the lowest Changeval to set to 1 and then the rest of the rows for a certain ID value to 0? You also want the changeval set to 0? Then for value2, you set the same row to 1 and others to 0?
If you use a windowing function, you can get the first_value() of the row or the min(). Use the OVER() clause and partition by your ID, but order by changeval. You'd use something like this:
SELECT ID, CName, Value1, Changeval, Value2
, MIN(changeval) OVER (PARTITION BY ID ORDER BY changeval)
, FIRST_VALUE(changeval) OVER (PARTITION BY ID ORDER BY changeval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM GetMax
You can then put that in a CTE to find the values of the ID and changeval that need to be updated. If there is a match, update to 1, otherwise 0.
August 28, 2018 at 7:25 am
It doesn't matter about the value of ChangeVal. I want to just update one row with the MAX of Value1 and make all others 0 for each ID.
Similarly update one row with the MAX of Value2 and make all others 0 for each ID. For a particular ID if Value1 or Value2 has only 0 values then all be 0 else update one row with max value and others 0.
August 28, 2018 at 7:42 am
Those requirements don't quite make sense, especially given your results. If you have multiple rows, then do you pick a random one? That seems incomplete and you ought to be sure this is correct. If that's the case, is it the same row to change both value1 and value2? What do you mean by MAX?
If there is one row for an ID, then what?
If all row s for an id have 0s, then you don't do anything, correct?
Your requirements are slightly vague, and seem incomplete. When you write something, remember that none of us know what you're working with or how you're thinking, so you need to explain clearly. Don't assume we can interpret a short sentence.
Also , I'd like to see you take my SELECT and try something. Whether this is homework or a work assignment, you ought to understand how things work and practice gaining some knowledge. Use your requirements to set filters in the WHERE clause or in the CASE statements.
August 28, 2018 at 8:43 am
I think this will get you started. You'll need to put this in a CTE if you actually want to update the table.
SELECT *,
CASE
WHEN Changeval = MIN(ChangeVal) OVER(PARTITION BY CName)
THEN MAX(Value1) OVER(PARTITION BY CName)
ELSE 0
END AS Value1_Update
FROM #GetMax
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply