关于特定查询的ROW_NUMBER

I have the below data, and I performed a ROW_NUMBER(partition by ID order by YEAR) function based on year which's ranking my data as below. I want to bring in name for every id based on their latest year. I want to bring in NULL data if that's the only data available and bring in latest NON NULL data for every other record. But rownumber only lets me bring in recent name which could be NULL. How do I query below data to bring in most recent NON NULL name?

我有以下数据,并且我根据年份对我的数据进行排名,执行了ROW_NUMBER(按YEAR的ID顺序划分)功能,如下所示。我想根据他们最近的一年为每个id带来名字。如果这是唯一可用的数据,我想引入NULL数据,并为每个其他记录引入最新的NON NULL数据。但是rownumber只允许我引入最近可能为NULL的名称。如何查询以下数据以引入最新的NON NULL名称?

ID  year  name  rownum
10 2011   abc    1
10 2010   abc    2
11 2011   ghi    1
11 2010   ghi    1
13 2010   NULL   1
13 2009   jkl    2
14 2014   NULL   1
14 2014   mno    2
15 2015   NULL   1

I want to bring in names jkl, mno for ID's 13 and 14 and not NULLS in my final result. Any suggestion on how to achieve that?

我想引入ID为13和14的名字jkl,mno而不是我最终结果中的NULLS。关于如何实现这一点的任何建议?

The output I desire is below - I want to display data for ROW NUM=1

我想要的输出在下面 - 我想显示ROW NUM = 1的数据

10 2011  abc  
11 2011  ghi
13 2009  jkl
14 2014  mno
15 2015  NULL

1 个解决方案

#1

Sort non-null rows ahead of null rows:

在空行之前排序非空行:

select ID, year, name
from (select *,
        row_number() over (partition by ID 
            order by case when name is null then 1 else 0 end, year desc) as RN
    from #t) _
where rn = 1

另请参阅SQL Server等效于Oracle的NULLS FIRST?,SQL Server ORDER BY date和nulls last&

#1

Sort non-null rows ahead of null rows:

在空行之前排序非空行:

select ID, year, name
from (select *,
        row_number() over (partition by ID 
            order by case when name is null then 1 else 0 end, year desc) as RN
    from #t) _
where rn = 1

另请参阅SQL Server等效于Oracle的NULLS FIRST?,SQL Server ORDER BY date和nulls last&

秒客网

关于特定查询的ROW_NUMBER

1 个解决方案

#1

#1

相关文章