排名的子查询没有给出所需的结果Sybase ASE

Question

具有排序函数的SQL QUERY：

select availability_stat from (select availability_stat, row_number() 
over (partition by sop_ckey order by 
case availability_stat when 'ONLINE' then 1 
when 'NEARLINE' then 2 when 'OFFLINE' then 3 end) 
as rownumber from arc_address where study_ckey = 849) 
as arc_address_with_rownumber 
where rownumber = 1 group by availability_stat

输出：

study_ckey|sop_ckey|availability_stat|rownumber|
----------|--------|-----------------|---------|
849|     955|NEARLINE         |        1|
849|     955|ONLINE           |        3|
849|     955|OFFLINE          |        2|
849|     957|NEARLINE         |        1|
849|     960|NEARLINE         |        1|
849|     960|ONLINE           |        2|
849|     963|NEARLINE         |        1|
849|     963|OFFLINE          |        2|
849|     971|NEARLINE         |        1|
849|     971|ONLINE           |        2|
849|     973|NEARLINE         |        1|
849|     973|OFFLINE          |        2|

SYBASE QUERY尝试使用子查询：

select aa.study_ckey,aa.sop_ckey,  aa.availability_stat,
(select count(distinct availability_stat) 
from arc_address aa2
where aa2.sop_ckey = aa.sop_ckey
and aa2.availability_stat < aa.availability_stat 
or aa2.availability_stat = aa.availability_stat ) as rownumber
from arc_address aa 
where aa.study_ckey = 849 
order by sop_ckey

输出：

study_ckey|sop_ckey|availability_stat|rownumber|
----------|--------|-----------------|---------|
849|     955|NEARLINE         |        1|
849|     955|ONLINE           |        3|
849|     955|OFFLINE          |        2|
849|     957|NEARLINE         |        1|
849|     960|NEARLINE         |        1|
849|     960|ONLINE           |        2|
849|     963|NEARLINE         |        1|
849|     963|OFFLINE          |        2|
849|     971|NEARLINE         |        1|
849|     971|ONLINE           |        2|
849|     973|NEARLINE         |        1|
849|     973|OFFLINE          |        2|

排名没有按预期进行，即

在线-1，近线-2，脱机-3我可能没有在这里使用case语句，但是case语句甚至会恶化结果集。请提出建议。

Answer 1

实现相同并共享解决方案

select sop_ckey,availability_stat, case when availability_stat = 'ONLINE' then 1
when availability_stat = 'NEARLINE' then 2
when availability_stat = 'OFFLINE' then 3
end as rank_value
into #tmp
from arc_address aa1 where study_ckey = 849
group by sop_ckey,availability_stat

select availability_stat
from (
select availability_stat,(select 1 + count(distinct t2.availability_stat)
from #tmp t2
where t2.sop_ckey = t1.sop_ckey and 
t2.rankvalue < t1.rankvalue) as new_rank
from #tmp t1
)
as arc_address_with_rownumber
where  newrank = 1
group by availability_stat