Alternate approach for row_number-CodePudding

Suppose I have this:

Id  Code  Inactive
1   A     0
2   A     1
3   B     0
4   C     1

And I want this as result:

Id  Code  Inactive
2   A     1
3   B     0
4   C     1

What is desired:
For each Code if there is an Inactive record, return it, otherwise return active one (Inactive = 0)

Why alternate approach for row_number:
I'd have a parameter which tells should just return active records or gives priority to inactive ones.

-- When InactiveHasPriority is false
select * from tbl where Inactive = 0

and now if ReturnInactiveIfExists = true, I'm looking for a simpler (less query change) and still efficient way to do a query instead of something like this:

-- When InactiveHasPriority is true
select * from (select *, ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Inactive DESC) AS RowNo from tbl) t where t.RowNo = 1

looking for another approach to make second query as much as possible similar to the first one in order to not need to have much different queries for these two scenarios

CodePudding user response：

The requirement to make the two queries look as close as possible is quite odd, especially for such a simple query. It's unlikely you'll be able to come up with an alternative approach which is any closer.

What you can do is to turn the query into a CTE which is a bit easier to follow.

with tbl_active_first as (
  select
    *,
    ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Inactive DESC) AS RowNo
  from tbl
)
select *
from tbl_active_first
where RowNo = 1

And if you want to reuse it, make a view.

create view tbl_active as
  with tbl_active_first as (
    select
      *,
      ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Inactive DESC) AS RowNo
    from tbl
  )
  select *
  from tbl_active_first
  where RowNo = 1;

Then select from that.

select * from tbl_active