我有下表：
id a b
1 1 kate
1 4 null
1 3 paul
1 3 paul
1 2 lola
2 1 kim
2 9 null
2 2 null
結果應該是這樣的：
1 3 paul
2 1 kim
我想得到最后一個awhere b is not null。就像是：
select b
from (select,b
row_num() over (partition by id order by a desc) as num) as f
where num = 1
但是這樣我得到一個空值，因為最后a = 4對應于b IS NULL. 也許有一種方法可以重寫ffill熊貓的方法？
uj5u.com熱心網友回復：
假設：
a被定義NOT NULL。- 您需要具有最大
awhereb IS NOT NULL- perid的行。
SELECT DISTINCT ON (id) *
FROM tbl
WHERE b IS NOT NULL
ORDER BY id, a DESC;
db<>在這里擺弄
詳細解釋：
- 選擇每個 GROUP BY 組中的第一行？
uj5u.com熱心網友回復：
嘗試：
select id, a, b
from (select id, a, b,
row_num() over (partition by id order by a desc nulls last) as num
from unnamedTable) t
where num = 1
或者，如果這不正確，請嘗試使用nulls first. 我永遠不記得它與desc.
uj5u.com熱心網友回復：
如果您不能保證每個至少有一個非空值，id那么您需要將空值移到串列的底部，而不是完全過濾掉這些行。
select id, a, b
from (
select id, a, b,
row_number() over (
partition by id
order by case when b is not null then 0 else 1 end, a desc
) as num
) as f
where num = 1
uj5u.com熱心網友回復：
如果您希望保持原始列不變，您可以將其環繞 acte并將join其回傳到主表，但查看您的預期輸出和邏輯，應該這樣做。話雖如此，row_number()基于方法可能會快一點。
select distinct
id,
max(a) over (partition by id) as a,
first_value(b) over (partition by id order by a desc) as b
from tbl
where b is not null;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/323646.html
標籤：sql PostgreSQL 每组最大 n
上一篇：使用Python的正則運算式