SQL获取列唯一的所有列

Question

我需要从donor_id唯一的dpgift中选择所有列。我知道如何使用以下命令从dpgift获取唯一的donor_id记录：

SELECT distinct dpgift.donor_id from dpgift join dp on dp.donor_id = dpgift.donor_id

(我将加入dp表以获取更多的列字段)。现在，如何根据最后一个gift_date获取每个唯一donor_id的所有列

预期输出(有100+实际列)：

0 => [
"donor_id" => "3838"
"gift_date" => "5/11/2018"
"amount" => "20"
"total" => "0"
"bill" => "0"
"balance" => "0"
"created_date" => "5/31/2017"
],
1 => [
"donor_id" => "3839"
"gift_date" => "12/11/2019"
"amount" => "155"
"total" => "120"
"bill" => "0"
"balance" => "0"
"created_date" => "1/8/2018"
]

当前输出：

0 => [
"donor_id" => "3838"
"gift_date" => "4/6/2017"
"amount" => "5"
"total" => "0"
"bill" => "0"
"balance" => "0"
"created_date" => "5/31/2017"
],
1 => [
"donor_id" => "3838"
"gift_date" => "5/11/2018"
"amount" => "20"
"total" => "0"
"bill" => "0"
"balance" => "0"
"created_date" => "5/31/2017"
]

Answer 1

现在，如何根据最后一个donor_id获取每个唯一gift_date的所有列？

一种方法是row_number()

select g.*
from (select g.*, dp.*,    -- select the column you want if there are duplicates
             row_number() over (partition by dp.donor_id order by g.gift_date desc) as seqnum
      from dpgift g join
           dp
           on g.donor_id = dp.donor_id
     ) g
where seqnum = 1;

Answer 2

使用Microsoft SQL，我找到了这个问题的答案，类似于戈登的答案。把它贴在这里，以防其他人在寻找答案。

Select * 
from 
(Select *, row_number() over (partition by dpgift.donor_id order by dpgift.gift_date) 
as donor from dpgift) t  
join dp on dp.donor_id = t.donor_id 
where donor = 1