Update statement to designate highest volume amount

[hedgracer]

I have the following table:

UseAcct UseName Vol Allocation
12917950 12917950 158
129CTTWL 12917950 4043
18511112 18511112 5690
24515576 245TOM 131
24535027 245TOM 5000

I need to put an "X" beside the UseAcct that has the highest Vol in a group of UseName (Example: 129CTTWL has the highest Vol in UseName group 12917950). In situations like UseAcct 18511112 the "X" would be placed beside the 5690. How would I do this in TSQL? BTW, I am on SQL Server 2005. Any help would be appreciated.

 

[Aluminum]

-- Create Sample Table
CREATE TABLE dbo.thetable (UseAcct VARCHAR(15),
UseName VARCHAR(10),
Vol INT)
-- Insert your test data
INSERT INTO thetable
SELECT '12917950', '12917950', 158
UNION SELECT '129CTTWL', '12917950', 4043
UNION SELECT '18511112', '18511112', 5690
UNION SELECT '12917950' ,'12917950' ,158
UNION SELECT '24515576' ,'245TOM', 131
UNION SELECT '24535027', '245TOM' ,5000

-- Perform the update
UPDATE thetable
SET thetable.UseAcct = thetable.UseAcct + 'X'
FROM thetable
INNER JOIN (SELECT UseAcct,
UseName,
Vol,
ROW_NUMBER() OVER (PARTITION BY USENAME ORDER BY Vol DESC) AS HighestVal
FROM TheTable) AS Filter
ON thetable.useacct = filter.useacct AND filter.HighestVal = 1

 

[Aluminum]

The above solution assumes the UseAcct is a unique value. Hope this works for you. The partition by functions are great if you have never used them.

 

[NastiaMurzin]

You can change the table by recreating the table again, but this time with the appropriate column. Here is an example on how u can create the table, but don't forget other columns.
use tablename;
go
if dbo.tablename is not Null
drop table dbo.tablename;
go
create table dbo.tablename (columnx varchar(1) null);
go
insert into dbo.tablename (columnx) values "X";
go
select columnx from dbo.tablename;
go