Select specific user only if they are a "duplicate" and meet condition-CodePudding

I have a result set like this:

UserName   Action
-------------------
Joe        Add
Joe        Remove
Fred       Add
Sam        Add
Carl       Remove

I only want to return the Add for Joe in this case. Add takes precedence. So if the user has an add record we only return that like.

Joe   Add
Fred  Add
Sam   Add
Carl  Remove

Not sure the best way to accomplish this.

CodePudding user response：

If your real example is really as simple as your post, maybe consider something like:

SELECT username, MIN(action) action
FROM mytable
GROUP BY username

That will return one row per username (because of the GROUP BY). If there is some mix of "Add"/"Remove" actions, it will pick add (because "Add" will be the MIN). If there is only a "Remove" action (no "Add"s) it will use that.

CodePudding user response：

Yet another option (a nudge less performant than Eric's) is using WITH TIES

Select top 1 with ties * 
 From  YourTable
 Order By row_number() over (partition by UserName order by Action)

CodePudding user response：

I'd use a CTE to isolate the rows I'm interested in by assigning ROW_NUMBER() values.

WITH cte AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY Action) AS RowNum
   ,UserName
   ,Action
  FROM Users
)
SELECT 
  UserName
 ,Action
FROM
 cte
WHERE RowNum = 1;

Here's the test harness:

CREATE TABLE Users
    ([UserName] varchar(4), [Action] varchar(6))
;
    
INSERT INTO Users
    ([UserName], [Action])
VALUES
    ('Joe', 'Add'),
    ('Joe', 'Remove'),
    ('Fred', 'Add'),
    ('Sam', 'Add'),
    ('Carl', 'Remove')
;

Here's a SQL Fiddle

Edit: The ORDER BY clause in the ROW_NUMBER works here because of alphabetical order. If the list of actions is longer, you might need to impose an order with a CASE expression. For instance:

...ORDER BY CASE WHEN Action = 'Add' THEN 1 ELSE 2 END

Which might be a good idea anyway, to future-proof your sort order.