从父样本中选择n个样本

Question

我有一张有层次结构的样品的桌子。字段sample_number是关键，父示例由字段parent_aliquot标识。对于给定的父样本，有0到20个别名样本。每个also“示例也按字段aliquot_number在其父样本中索引。我可以为父母样本选择所有的别名：

select s2.sample_number, s1.sample_number, s1.aliquot_number
from sample s1
inner join sample s2 on s2.sample_number = s1.parent_aliquot
order by s2.sample_number, s1.aliquot_number desc

但是我想为每个父样本选择所有的别名样本，但是2(或n)，留下两个样本的最低aliquot_number (可能不是1和2)。这是如何做到的呢？

Answer 1

您可以在以下方面使用ROW_NUMBER：

select sample_number, sample_number, aliquot_number
from (
   select s2.sample_number, s1.sample_number, s1.aliquot_number,
          row_number() over(partition by parent_aliquot order by aliquot_number) rn
   from sample s1
   inner join sample s2 on s2.sample_number = s1.parent_aliquot
     )x
where rn <= 2 -- here you specify how many rows you want to retrieve
order by sample_number, aliquot_number desc