Using a self join:

如果存在多种因素,所有价值将归并到底价值。

SELECT a.value
  FROM TABLE a
  JOIN (SELECT MAX(t.subkey) AS max_subkey
          FROM TABLE t
         WHERE t.key = 1) b ON b.max_subkey = a.subkey
 WHERE a.key = 1

Using RANK & CTE (SQL Server 2005+):

如果存在多种因素,所有价值将归并到底价值。

WITH summary AS (
  SELECT t.*,
         RANK() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using ROW_NUMBER & CTE (SQL Server 2005+):

这将交还一行,即使有一个以上具有相同关键价值......。

WITH summary AS (
  SELECT t.*,
         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using TOP:

这将交还一行,即使有一个以上具有相同关键价值......。

  SELECT TOP 1
         t.value
    FROM TABLE t
   WHERE t.key = 1
ORDER BY t.subkey DESC

非常简单,没有加入,没有分局:

SELECT FIRST_VALUE(Value) OVER (ORDER BY SubKey DESC)
FROM TableA
WHERE Key = 1

如果您需要每个关键部分的最高价值:

SELECT DISTINCT Key, 
FIRST_VALUE(Value) OVER (PARTITION BY Key ORDER BY SubKey DESC)
FROM TableA

SELECT MAX(Value)
FROM TableA t1
GROUP BY Key, SubKey
HAVING SubKey = (SELECT MAX(SubKey) FROM TableA t2 WHERE t1.Key = t2.Key)
  AND Key = 1

OMG 他们打上了大多数这样做的方法。 这里还有一点:

SELECT
    T1.value
FROM
    My_Table T1
LEFT OUTER JOIN My_Table T2 ON
    T2.key = T1.key AND
    T2.subkey > T1.subkey
WHERE
    T2.key IS NULL

T2.key将成为NUL的唯一时间是在LEFT JOIN没有配对,这意味着没有上下调。 如果有多个网点(最重)为主,则会返回多个行。

其中一个缺失之处是,如果还有多个关键因素,在必须交还每个最大值的子钥匙时,如何做。 简单地加入<条码>摘要。 附有<条码>MIN和GROUP的表格,“自发”,在座。

WITH summary AS (
  SELECT t.*,
         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.*
  FROM summary s
  join  (select key, min(rank) as rank
        from summary
        group by key) sMAX
        on s.key = sMAX.key and r.rank = sMAX.rank

如果你总是只想一行一面重要价值,而不是一劳永逸地回答许多关键因素,那么,所有进退步都对建设毫无用处。 仅用“TOP 1”查询OMG Ponies已经给你。

在使用计算机辅助设备时:

WITH CTE AS
(
    SELECT key1, key2, MAX(subkey) AS MaxSubkey
    FROM TableA 
    GROUP BY key1, key2
)
SELECT a.Key1, a.Key2, a.Value
FROM TableA a
    INNER JOIN CTE ON a.key1 = CTE.key1 AND a.key2 = CTE.key2 AND
                      a.subkey = CTE.MaxSubkey

可能更快地“第一价值”:

select distinct on ("Key") "Key", "Value"
from tablea
order by "Key", "SubKey" desc