sql - Query to find rows with nearest date in future
问题描述
I'm trying to display a result set based on a min date value and today's date but can't seem to make it work. It's essentially a date sensitive price list.
Example Data
ID Title Value ExpireDate
1 Fred 10 2019-03-01
2 Barney 15 2019-03-01
3 Fred2 20 2019-06-01
4 Barney2 25 2019-06-01
5 Fred3 30 2019-07-01
6 Barney3 55 2019-07-01
Required Results:
Display records based on minimum date > GetDate()
3 Fred2 20 2019-06-01
4 Barney2 25 2019-06-01
Any assistance would be great - thank you.
解决方案
Use where clause to filter all future rows and row_number()
to find the first row per group:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Title ORDER BY ExpireDate) AS rn
FROM t
WHERE ExpireDate >= CAST(CURRENT_TIMESTAMP AS DATE)
) AS x
WHERE rn = 1
Based on your revised question, you can simply do this:
SELECT TOP 1 WITH TIES *
FROM t
WHERE ExpireDate >= CAST(CURRENT_TIMESTAMP AS DATE)
ORDER BY ExpireDate
推荐阅读
- linux - 通过 Rundeck 在屏幕中运行脚本
- powershell - Powershell 脚本 - 根据用户名或电子邮件地址从 AD 获取专有名称
- julia - 从 Julia 中的 Process Return 修剪换行符`\n`
- linux - add_custom_command 中的 CMake 命令无法运行
- c - 如何使用包含 X11/Xlib.h 的 C 程序将字符串绘制到窗口?
- flutter - 如何使用 Provider 包监听 TextField 的变化?
- git - 确保通过 Plesk 上的 Git 更新时 Bin 文件权限不会更改
- deno - 在“Deno.readFile Method()”下出现错误
- django - Python中的超级
- batch-file - 将带有通配符的绝对文件路径设置为 CMD .bat 文件中的变量