Postgress查询，分组依据，并显示最新的时间戳

Question

我在一个包含时间戳的表上进行查询。

我当前的查询是下一个

SELECT MAX (time) as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
GROUP BY name, country, tocurrency, fromcurrency, exchangeratebuy; 

如何在此查询中显示exchangeratebuy的值而不通过exchangeratebuy发出Gruoping？

我想做一些类似的东西：

SELECT MAX (time) as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
GROUP BY name, country, tocurrency, fromcurrency; 

但这给了我一个错误，即所选列也需要按分组

Answer 1

如果您希望每个组有一条记录，请使用DISTINCT ON：

SELECT DISTINCT ON (name, country, tocurrency, fromcurrency, exchangeratebuy)
time as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
ORDER BY BY name, country, tocurrency, fromcurrency, exchangeratebuy, time DESC; 

"组"由所有初始列定义。最后一列定义哪个行取自组。

在您的情况下，您可能只想要一排。为此，您还可以使用ORDER BY和LIMIT:

SELECT
time as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
ORDER BY BY time DESC
LIMIT 1;