Adding values based on duplicated data

Question

it's my first post and I hope everything will be clear for you. I've got table such as:

client ; cost 
Paula  ; 100
Paula  ; 50
Jacob  ; 300
Paula  ; 120

I want to add another column "client2" where value will be 1 if there wasn't duplicates or if there was duplicate it should 1 only for the duplicate with the biggest cost, so:

client ; cost ; client2
Paula  ; 100  ; 0
Paula  ; 50   ; 0
Jacob  ; 40   ; 1
Paula  ; 120  ; 1

In real table there are 2000 records. How should I write it in SQL Server? Thanks in advance, Paula

Answer 1

WITH CTE AS
    (SELECT
        client,
        cost,
        ROW_NUMBER() OVER (PARTITION BY client ORDER BY cost DESC) AS RowNo
    FROM
        table)
SELECT
    client,
    cost,
    CASE RowNo
        WHEN 1 THEN 1
        ELSE 0
    END AS client2
FROM
    CTE;

Answer 2

You can do this with a cursor. The below example should handle what you want. You'd have to replace #table with the name of your table. Ugly as sin but it works ;)

 CREATE TABLE #Table
(
  client NVARCHAR(100),
  cost int,
  client1 int
);

INSERT INTO #Table
VALUES
('Paula',100,null),
('Paula',50,null),
('Jacob',300,null),
('Paula',120,null);

DECLARE @clientColumn nvarchar(100);
DECLARE @Dup int;

DECLARE client_cursor CURSOR FOR 
SELECT client,cost FROM #Table ORDER BY client,cost

OPEN client_cursor;
FETCH NEXT FROM client_cursor INTO @clientColumn

WHILE @@FETCH_STATUS=0
BEGIN

set @dup = (SELECT TOP 1 cost FROM #Table WHERE client = @clientColumn GROUP BY client ORDER BY cost desc )

UPDATE #Table
SET
    client1 = 1 
WHERE client = @clientColumn AND cost = @dup     

FETCH NEXT FROM client_cursor INTO @clientColumn
END
CLOSE client_cursor 
DEALLOCATE  client_cursor;

UPDATE #Table
SET
    client1 = 0
    WHERE client1 IS NULL

SELECT * FROM #Table