SQL - aggregate function to get value from same row as MAX()
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 sele开发者_如何学JAVAct list because it is not contained in either an aggregate function or the GROUP BY clause
How should I do this correctly?
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)
you must include 'v.timestamp' in the Group By clause. Hope this will help for you.
精彩评论