程式隨筆
0%

【SQL】Group by and Top 1

Posted on Edited on Disqus:

參考：[SQL] 於多筆重複資料中取得該重複群組中最新一筆資料

今天也遇到一個需求是將group by完後的資料挑出最新的一筆
模擬的原始資料大致如下

我希望抓出每個人在在每個地點所做的最後一件事情，寫法如下:

  • 先將每個人跟地點透過時間由大到小歸類 ```sql
    select * ,ROW_NUMBER() over(partition by location,who order by time Desc ) as row_index
           from timeline

1
2
3
4
5
6
7
         [![](http://3.bp.blogspot.com/-hbcc3sD90ek/UaXGjjKL0UI/AAAAAAAADmw/jh2ks4iABqM/s1600/%E6%9C%AA%E5%91%BD%E5%90%8D.png)](http://3.bp.blogspot.com/-hbcc3sD90ek/UaXGjjKL0UI/AAAAAAAADmw/jh2ks4iABqM/s1600/%E6%9C%AA%E5%91%BD%E5%90%8D.png)*   然後        ```sql
select *
         from (
              select *  ,ROW_NUMBER() over(partition by location,who order by time Desc ) as row_index
              from timeline) as tempTable
         where tempTable.row_index = 1
       
   [![](http://4.bp.blogspot.com/-E-Syl1jp57A/UaXHnZ_4jwI/AAAAAAAADm8/4cLCGkPmMag/s1600/%E6%9C%AA%E5%91%BD%E5%90%8D.png)](http://4.bp.blogspot.com/-E-Syl1jp57A/UaXHnZ_4jwI/AAAAAAAADm8/4cLCGkPmMag/s1600/%E6%9C%AA%E5%91%BD%E5%90%8D.png)*   結論就是早點回家，否則也只是在公司吃東西而已 XD