时间戳

首页 > 解决方案 > 选择 max dense_rank() = value 的所有位置？

sql - 选择 max dense_rank() = value 的所有位置？

问题描述

我有使用dense_rank的代码，按ID分区并按val2排序。我想选择任何给定 ID 的最大排名为 2 的所有行。

下面的代码为初始步骤提供了正确的输出。

Select distinct 
`account id`, `val2`, distinct_rank, observation_total
from ( Select 
`account id`, `val2`, dense_rank() 
over (partition by `account id` order by `val2` desc) as distinct_rank,
count(*) 
over (partition by `account id` order by `val2` desc) as observation_total
from TABLE1 );

样本结果：

account id | val2 | distinct_rank 
___________________________________
1          | a    | 1
2          | a    | 1
2          | b    | 2
2          | c    | 3
3          | d    | 1
3          | e    | 2

所以我需要一个查询，它只选择每个帐户 id 的最大 distinct_rank = 2 或其他值的行。

例如，如果 max distinct_rank per account id = 2，结果将是：

account id | val2 | distinct_rank 
___________________________________
3          | d    | 1
3          | e    | 2

This is because account id = 3 has a max distinct_rank of 2.

我知道如何选择 max rank = 2，我可以只做一个查询，上面写着“select * where account id in a subquery that grab all with max distinct_rank = 2”，但我觉得可能会有更好/更有效方法？

标签： sqlsql-server

解决方案

试试CROSS APPLY运算符：

select
  *
from table1 a
cross apply(
  select count(distinct val2) dist_qnt
  from table1 b where b.[account id] = a.[account id]
) ca
where ca.dist_qnt = 2;

或者使用这篇文章中的方法。

select *
from (
  select
    *,
    dense_rank() over(
      partition by [account id] order by val2
    ) +
    dense_rank() over(
      partition by [account id] order by val2 desc
    ) - 1 as dist_qnt
  from table1
) sq
where dist_qnt = 2;

但要考虑 NULL 值。

SQL Fiddle上的演示。

推荐阅读