CODEWITHSUNDEEP
sqlsql-server
Limna
Limna

Reputation: 403

SQL: Select Query with Conditions

I have a table with data as below:

enter image description here

From that table I want to get output like:

enter image description here

Actual requirements is to show Entity with both IsDefault and IsOwner as "Y".

If no data with that condition, then the next condition is select Entity with IsDefault is "Y".

If no, then select Entity with IsOwner is "Y".

If no Entity with value "Y", that Entity will not be shown.

Each Entity shown atleast once.

I tried with below query:

SELECT ENTITY_ID,IS_DEFAULT,IS_OWNER FROM #TEMP_CHART WHERE (IS_DEFAULT='Y' OR IS_OWNER ='Y')

But showing same Entity two times as below:

enter image description here

Is there any select query to get above output

Upvotes: 2

Views: 106

Answers (3)

Ashwin Golani
Ashwin Golani

Reputation: 421

enter image description here

try to execute above code.. you will get the result..

Upvotes: -1

Ullas
Ullas

Reputation: 11556

You can give a rank by DENSE_RANK based on the IsDefault and IsOwner columns.

Query

;with cte as(
    select [rn] = dense_rank() over(
        partition by [Entity]
        order by 
          case [IsDefault] when 'Y' then 1
          else 2 end,
          case [IsOwner] when 'Y' then 1
          else 2 end
    ), * 
    from [your_table_name]
)
select [Entity], [IsDefault], [IsOwner]  from cte
where [rn] = 1;

Find demo here

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

You can use rank() window function to get the data as require:

select *
from (
    select t.*,
        rank() over (
            partition by entity order by isDefault desc,
                IsOwner desc
            ) rn
    from your_table t
    where isDefault = 'Y'
        or IsOwner = 'Y'
    ) t
where rn = 1

The ordering in the window function is very important here.

Demo

Upvotes: 2

Related Questions