T-SQL：OVER中的WHERE子句（PARTITION BY ... ORDER BY ...）

I have the following query

我有以下查询

;WITH tmp AS
(
    SELECT *, ROW_NUMBER()
    OVER
        (PARTITION BY to_tel, duration, call_date 
        ORDER BY rates_start DESC) as rn
    FROM ##TempTable
)
SELECT *
FROM tmp
WHERE rn = 1
ORDER BY customer_id, to_code, duration

But I would like to modify it where it doesn't give me the maximum rates_start, but the maximum rates_start before a certain date. Is there any way I can do this?

但是我想修改它,它不会给我最大的rates_start,但是在某个日期之前的最大rates_start。有什么方法可以做到这一点吗?

1 个解决方案

#1

You can add WHERE inside the cte part. I'm not sure if you still want to partition by call_date in this case (I removed it). Change the PARTITION BY part if needed.

您可以在cte部分中添加WHERE。在这种情况下,我不确定你是否还想通过call_date进行分区(我删除了它)。如果需要,更改PARTITION BY部分。

;WITH tmp AS
(
    SELECT *, ROW_NUMBER()
    OVER
        (PARTITION BY to_tel, duration
        ORDER BY rates_start DESC) as rn
    FROM ##TempTable
    WHERE call_date < @somedate
)
SELECT *
FROM tmp
WHERE rn = 1
ORDER BY customer_id, to_code, duration