Gostgres SQL一周的记录列表

Question

data :::::::

21,600,false,203,203,666,'2017-11-23 13:10:56+05:30'
20,600,false,203,203,666,'2017-11-22 13:10:56+05:30'
19,600,false,203,203,666,'2017-11-21 13:10:56+05:30'
18,600,false,203,203,666,'2017-11-20 13:12:56+05:30'
17,120,false,203,203,666,'2017-11-20 13:10:56+05:30'

---

select * 
from progress_Report 
where programid = 666 and datedone>='2017-11-19 00:00:00.000000 +05:30:00'  
and datedone<='2017-11-27 00:00:00.000000 +05:30:00' 
order by datedone desc

如果您检查数据，这是我的查询，对于日期2017-11-20，我需要最新的更新值，但是我要获得所有插入的记录。

Answer 1

尝试在日期使用ROW_NUMBER与分区：

SELECT col1, col2, ...
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY datedone::date ORDER BY datedone DESC) rn
    FROM progress_Report
    WHERE programid = 666
) t
WHERE rn = 1;

鉴于查询中的日期范围包括所有记录，至少您在问题中显示的记录。