sql - 选择第二个最大值

Question

我想从 tblTask​​s(JobID, ItemName, ContentTypeID) 中选择第二高的值

这就是我的想法。我敢打赌它可以做得更容易，但我不知道怎么做。

SELECT Max(JobID)    AS maxjobid,
       Max(ItemName) AS maxitemname,
       ContentTypeID
FROM   
      (SELECT JobID, ItemName, ContentTypeID
      FROM tblTasks Ta
      WHERE JobID NOT IN
            (SELECT MAX(JobID)
            FROM tblTasks Tb
            GROUP BY ContentTypeID)
            ) secmax

     GROUP BY secmax.ContentTypeID

Answer 1

我猜你会想要这样的东西。

SELECT  JobID AS maxjobid,
        ItemName AS maxitemname,
        ContentTypeID
FROM    (SELECT JobID,
                ItemName,
                ContentTypeID,
                ROW_NUMBER() OVER (PARTITION BY ContentTypeID ORDER BY JobID DESC) Rn
         FROM   tblTasks Ta

        ) t
WHERE Rn = 2

这将为您提供每个 ContentTypeID 的第二高 JobID 记录