SQL - 聚合函数从与 MAX() 相同的行获取值

发布于 2024-09-09 05:07:30 字数 481 浏览 3 评论 0原文

我有一个表，其中包含通道、值和时间戳列，另一个表包含其他 7 个列，其中包含各种数据。

我将这两个连接在一起，我想选择一小时内值列的最大值以及相应行的时间戳。这是我尝试过的，但它（显然）不起作用。

SELECT
    v.channel,
    MAX(v.value),
    v.timestamp,
    i.stuff,
    ...
FROM
    Values v
INNER JOIN
    @Information i
ON i.type = v.type
GROUP BY channel, DATEPART(HOUR, timestamp), i.stuff, ...

我（并不奇怪）收到以下错误：

“dbo.Values.timestamp”在选择列表中无效，因为它未包含在聚合函数或 GROUP BY 子句中

我应该如何正确执行此操作？

原文

I have one table with columns channel, value and timestamp, and another table with 7 other columns with various data.

I'm joining these two together, and I want to select the maximum value of the value column within an hour, and the timestamp of the corresponding row. This is what I've tried, but it (obviously) doesn't work.

SELECT
    v.channel,
    MAX(v.value),
    v.timestamp,
    i.stuff,
    ...
FROM
    Values v
INNER JOIN
    @Information i
ON i.type = v.type
GROUP BY channel, DATEPART(HOUR, timestamp), i.stuff, ...

I'm (not very surprisingly) getting the following error:

"dbo.Values.timestamp" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

How should I do this correctly?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

飘落散花 2024-09-16 05:07:30

您可以使用 RANK() 或 DENSE_RANK() 功能来获取适当的结果。像这样的东西：（

;WITH RankedResults AS
(
    SELECT
        channel,
        value,
        timestamp,
        type,
        RANK() OVER (PARTITION BY DATEPART(hour,timestamp) ORDER BY value desc) as Position
    FROM
        Values
)
SELECT
   v.channel,
   v.value,
   v.timestamp,
   i.stuff
   /* other columns */
FROM
   RankedResults v
       inner join
   @Information i
       on
           v.type = i.type
WHERE
   v.Position = 1

是否使用 RANK 还是 DENSE_RANK 取决于您在平局的情况下想要做什么，真的）

（编辑 SQL 以包含连接，以响应 Tomas 的评论）

You could use the RANK() or DENSE_RANK() features to get the results as appropriate. Something like:

;WITH RankedResults AS
(
    SELECT
        channel,
        value,
        timestamp,
        type,
        RANK() OVER (PARTITION BY DATEPART(hour,timestamp) ORDER BY value desc) as Position
    FROM
        Values
)
SELECT
   v.channel,
   v.value,
   v.timestamp,
   i.stuff
   /* other columns */
FROM
   RankedResults v
       inner join
   @Information i
       on
           v.type = i.type
WHERE
   v.Position = 1

(whether to use RANK or DENSE_RANK depends on what you want to do in the case of ties, really)

(Edited the SQL to include the join, in response to Tomas' comment)

回复收藏 0 原文