SQL-Postgres-返回最新日期的最大值 [英] SQL - Postgres - return maximum value for latest date

问题描述

这可能很简单，但是我是SQL新手，找不到确切的操作方法。

This might be simple but I'm new to SQL and couldn't find how to do this exactly.

我有下表：

我的要求如下：

我需要为每个频率（每月和每周）选择最新的日期和时间。其最新日期的最高版本。然后选择所有Dim& amp;的记录日期和版本与先前选择的相同的两个频率。

I need, for each Frequency (monthly & weekly), pick latest Date & its maximum Version of that latest Date. Then select records for all Dim & both Frequencies where Date and Version are same as the earlier picked.

例如：将有一个最新日期&每月频率和单日& 每周频率的最高版本。现在，对于所有Dim（在我们的示例中为A& B），只需返回Date&频率与之前相同。

For example: There will single latest date & its maximum Version for 'Monthly' Frequency & single date & its maximum Version for 'Weekly' Frequency. Now for all Dim (A & B in our case), just return data where Date & Frequency are same as earlier.

所以总共有4行：

• 每月昏暗


• 每周一次昏暗的 A


• 每月昏暗的 B


• 昏暗'B'每周

• Dim 'A' Monthly
• Dim 'A' Weekly
• Dim 'B' Monthly
• Dim 'B' Weekly

有人可以帮我吗？

我尝试使用以下查询，但未返回正确的值：

I tried using following query but it not returning correct values:

SELECT Dim, Frequency, Date, Version
               FROM   sample_tbl 
               WHERE  ( Frequency, Date, 
                        Version ) IN ( 
select Frequency, max(Date), max(Version)
from sample_tbl
group by 1
);

推荐答案

您可以将带有子查询的联接用于获取最大日期，然后是最高版本

you could use a join with a subquery for get max date and then the max version

select s.Dim, s.Frequency, s.Date, max(s.Version)
from sample_tbl s 
inner ( 
SELECT Dim, Frequency, max(Date) as max_date
FROM   sample_tbl 
group by Dim, Frequency
) t on t.Dim = s.Dim, t.Frequency = s.Frequency t.max_date = s.Date 
GROUP BY s.Dim, s.Frequency, s.Date ;

这篇关于SQL-Postgres-返回最新日期的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！