Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
afarooqui
Starting Member
2 Posts |
Posted - 2014-04-08 : 22:24:30
|
I recently had an interview and the interviewer has asked me to use a group by clause in an update statement. Here is the table and data: Table name: Customercust Size Geo SegmentBP LG EUAccenture LG USMSC MED USSclumbergerLG USSo the request is to update the Segment column with a unique number starting with number 1. So for BP it should be 1, Accenture it should be 2, MSC it should be 3, Sclumberger should be 1 since the size and geo are not unique. He asked me to use a group by size and geo to generate a unique number for each combination and than make the update. Help! |
|
afarooqui
Starting Member
2 Posts |
Posted - 2014-04-09 : 17:14:18
|
I wrote this query: update Customerset Segment= case WHEN cust='Accenture' THEN '1' WHEN cust='BP' THEN '2' WHEN cust='MSC' THEN '3' WHEN cust='Schlumberger THEN '1' END;and this one: update Customerset segment = CASE cust WHEN 'Accenture' THEN '1' WHEN 'BP' THEN '2' WHEN 'MSC' THEN 3 WHEN 'Schlumberger' THEN '1' ENDWHERE cust IN ('Accenture', 'BP', 'MSC','Schlumberger');However, my interviewer has asked me to try to group by size and geo to generate a unique number for each combination and than make the update. This solution would work for all values whereas the solution above only works for the specific values in the CASE statement. Can someone help me with writing a group by for this request? |
|
|
kostya1122
Starting Member
15 Posts |
Posted - 2014-04-09 : 18:53:30
|
something like this should workupdate x set x.segment = y.segmentfrom Customer as xinner join ( select size, geo, row_number() over (order by size + geo) as segment from Customer group by size, geo) as yon x.size = y.size and x.geo = y.geo |
|
|
|
|
|