SQL Server按最接近零的值排序

Question

我在一个表中有一些重复的值，我试图使用Row_Number来过滤它们。我想使用datediff对行进行排序，并根据最接近零的值对结果进行排序，但我正在努力考虑负值。

下面是数据样本和我当前的Row_Number字段(rn)列:

PersonID    SurveyDate  DischargeDate   DaysToSurvey    rn
93638       10/02/2015  30/03/2015      -48             1
93638       27/03/2015  30/03/2015      -3              2
250575      23/10/2014  29/10/2014      -6              1
250575      19/11/2014  24/11/2014      -5              2
203312      23/01/2015  26/01/2015      -3              1
203312      26/01/2015  26/01/2015      0               2
387737      19/02/2015  26/02/2015      -7              1
387737      26/02/2015  26/02/2015      0               2
751915      02/04/2015  04/04/2015      -2              1
751915      10/04/2015  25/03/2015      16              2
712364      24/01/2015  30/01/2015      -6              1
712364      26/01/2015  30/01/2015      -4              2

上面的select语句是:

select 
    PersonID, SurveyDate, DischargeDate, 
    datediff(dd,dischargedate,surveydate) days, 
    ROW_NUMBER () over (partition by PersonID 
                        order by datediff(dd, dischargedate, surveydate) asc) as rn
from 
    Table 1
order by 
    PersonID, rn

我要做的是改变排序顺序，使其显示如下:

PersonID    SurveyDate  DischargeDate   DaysToSurvey    rn
93638       27/03/2015  30/03/2015      -3              1
93638       10/02/2015  30/03/2015      -48             2
250575      19/11/2014  24/11/2014      -5              1
250575      23/10/2014  29/10/2014      -6              2

因此最接近DischargeDate的DaysToSurvey值被排序为rn 1。

这可能吗?

Answer 1

你很接近了。只需添加ABS()即可计算差值的绝对值:

ROW_NUMBER () OVER (
  PARTITION BY PersonID 
  ORDER BY abs(datediff(dd, dischargedate, surveydate)) asc
) AS rn

Answer 2

您可以使用abs来获取与0的距离:

select PersonID, SurveyDate, DischargeDate, datediff(dd,dischargedate,surveydate) days, 
   ROW_NUMBER () over (partition by PersonID order by abs(datediff(dd,dischargedate,surveydate)) asc) as rn
from Table 1
order by PersonID, rn

Answer 3

Add ABS():

select PersonID, SurveyDate, DischargeDate, datediff(dd,dischargedate,surveydate) days, 
   ROW_NUMBER () over (partition by PersonID order by ABS(datediff(dd,dischargedate,surveydate)) asc) as rn
from Table 1
order by PersonID, rn