首页 文章

SQL以获取每种类型的最新日期时间行

提问于
浏览 次
1

我需要一个非常像SELECT within SELECT with TOP的SQL查询的帮助 .
但是我需要最新日期和该日期时间戳的值 . 使用时

SELECT 
    (SELECT TOP(1) NumericValue 
     FROM Datum 
     WHERE ChannelId = test.ChannelId 
     ORDER BY [DateTime] DESC) AS NumericValue, 
    (SELECT TOP(1) [DateTime] 
     FROM Datum 
     WHERE ChannelId = test.ChannelId 
     ORDER BY [DateTime] DESC) AS DataTime, 
    ChannelId, Diag, ChannelDescription 
FROM 
    Channel as test 
WHERE 
    InstrumentID = 3

这给了我数值列中的最高值而不是最新值 . 它给了我最新的日期时间，但我也需要它的 Value .

sql sql-server greatest-n-per-group

3 回答

  • 0

    如果您只想要Max [DateTime]及其对应关系NumericValue，请尝试此操作

    SELECT TOP(1)
    test.ChannelId, test.Diag, test.ChannelDescription, test.[DateTime], sub.MaxNumericValueForDateTime
FROM
    Channel test
    CROSS APPLY 
    (
    SELECT MAX(NumericValue) AS MaxNumericValueForDateTime 
    FROM Channel 
    WHERE InstrumentID = 3 AND [DateTime]=test.DateTime
    ) sub
WHERE
    InstrumentID = 3
ORDER BY
    [DateTime] DESC;
    回复于
  • 0

    试试这个，

    SELECT DISTINCT a.ChannelId, a.Diag, a.ChannelDescription, a.NumericValue, a.DateTime
FROM Channel a
    INNER JOIN (SELECT ChannelId, MAX(DateTime) Max_DateTime 
            FROM Channel
            GROUP BY ChannelId) b ON a.ChannelId = b.ChannelId AND a.DateTime = b.Max_DateTime
WHERE a.InstrumentID = 3
    回复于
  • 0

    我想你可以使用这样的查询：

    ;WITH t AS (
    SELECT *
        , ROW_NUMBER() OVER (ORDER BY [DateTime] DESC) rn
    FROM Datum
)
SELECT 
    t.NumericValue, t.[DateTime]
FROM 
    Channel AS test CROSS JOIN t
WHERE
    t.rn = 1 AND
    test.InstrumentId = 3;
    回复于

相关问题