Filtering in SQL Server table on multiple columns

EDITED We have a table like below where there are multiple rows with same Name with different Ops

Deal	Name	Ops
ABC	A	NULL
ABC	A	NULL
ABC	B	NULL
ABC	B	NULL
ABC	B	Default
ABC	B	Default
ABC	C	NULL
ABC	C	NULL
ABC	C	Default
ABC	C	Default
ABC	C	Aggr
ABC	C	Aggr

We need to get rows with Default when both NULL and Default is tagged and Aggr when NULL, Default and Aggr are tagged

Expected output:

Deal	Name	Ops
ABC	A	NULL
ABC	A	NULL
ABC	B	Default
ABC	B	Default
ABC	C	Aggr
ABC	C	Aggr

We need to get this using a SQL query.

I have tried this one here:

WITH PriorityRanking AS
(
    SELECT 
        DENSE_RANK() OVER (PARTITION BY Deal, Name 
                           ORDER BY 
                              CASE COALESCE(Ops,NULL)
                                  WHEN 'Aggr' THEN 1
                                  WHEN 'Default' THEN 2
                                  WHEN NULL THEN 3
                              END) AS Rnk, 
        *
    FROM 
        Table_name  
)
SELECT Deal, Name, Ops 
FROM
    (SELECT Deal, Name, Ops, 
     FROM PriorityRanking 
     WHERE Rnk = 1)

But the rank is set to 1 for NULL values and so the final select is not working properly.

Please suggest what is the best way to get the required data

CodePudding user response：

I think you just need to use row_number and coalesce your NULL values to be ordered below the other values -

with r as (
    select *, Row_Number() over(partition by deal, name order by IsNull(Ops,'x') ) rn
    from t
)
select Deal, Name, Ops
from r
where rn = 1;

depending on your real data as mentioned you could also simply aggregate:

select Deal, Name, min(Ops) Ops
from t
group by Deal, Name;

CodePudding user response：

The below logic can work

WITH cte1 as
(

Select deal,name,isnull(Ops,'Z') as Col from table
),
cte2 as
(
Select *,row_number() over(Partition by Deal,name order by col) as rnbr from cte1 
)

Select deal,name,case when col='Z' then NUll else Col end as Ops from cte2 where rnbr=1

CodePudding user response：

You can replace NULL with a text and sort after that

After changing your data, the basic concept stays valid

But now you need DENSE_RANK like you had it in your original question, as SQL SERVER can't handle NULL, so replacing will help

WITH PriorityRanking AS
 (
 SELECT DENSE_RANK() OVER (PARTITION BY Name ORDER BY CASE ISNULL(Ops,'NULL')
                                                                    WHEN 'Aggr' THEN 1
                                                                    WHEN 'Default' THEN 2
                                                                    WHEN 'NULL'  THEN 3
                                                                    END) AS Rnk,*
 FROM Table_name  
 )
 SELECT [Deal], [Name], [Ops] FROM  PriorityRanking WHERE Rnk = 1

Deal | Name | Ops    
:--- | :--- | :------
ABC  | A    | null   
ABC  | A    | null   
ABC  | B    | Default
ABC  | B    | Default
ABC  | C    | Aggr   
ABC  | C    | Aggr

db<>fiddle here

WITH PriorityRanking AS
 (
 SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CASE ISNULL(Ops,'NULL')
                                                                    WHEN 'Aggr' THEN 1
                                                                    WHEN 'Default' THEN 2
                                                                    WHEN 'NULL'  THEN 3
                                                                    END) AS Rnk,*
 FROM Table_name  
 )
 SELECT Deal, Name, Ops FROM PriorityRanking where Rnk = 1

Deal | Name | Ops    
:--- | :--- | :------
ABC  | A    | null   
ABC  | B    | Default
ABC  | C    | Aggr

db<>fiddle here